August 6, 2009 at 10:24 am
All -
Quick (hopefully) question here...
I have a SQL 2000 Server (SP4), and every now and again the tempdb will grow dramatically overnight until it fills up the disk that it is on, causing all sorts of things to get fubar'd.
We do employ IBM / Datamirror Transformation Server software on the SQL Server to facilitate data replication from our IBM iSeries, but I am not sure that is the cause of it.
Based on some reading I have done, the tempdb will grow to accommodate large queries or inserts where the data is not commited gradually - essentially requiring the data to be temporarily stored in tempdb until the process is complete and the data can be committed. Is this correct? Are there any other scenarios that could cause tempdb to grow so rapidly, and is there any way for me to determine what process or statement might have contributed to the scenario?
Thanks so much!!
D
August 6, 2009 at 10:30 am
temp tables and work tables for sorting also have an affect on tempdb, so if you are using a lot of temp tables, table variables, or doing a lot of sorting in your queries, that can cause it to grow.
August 6, 2009 at 10:31 am
if it's possible, I would look at what is running on the server while the growth happens and you will be able to zero in on the true cause.
August 6, 2009 at 10:48 am
Thanks for the response...
Should I be just looking at PIDs, agent jobs, etc. or is there someplace else that might be easier? (Sorry I know that is a total noob question... but...)
August 6, 2009 at 3:05 pm
I would run sp_who2 while tempdb is growing and that will return all the active processes. if you want to see what statements are being run, you will need to run dbcc inputbuffer(PROCESSID), replacing PROCESSID with the actual ID number of the process you want to look at. Hope that helps.
August 6, 2009 at 3:08 pm
Jobs are also a good place to start, especially if the growth consistently happens at the same time of day. run select * from msdb.dbo.sysjobhistory to see if there was any jobs running during that time.
August 6, 2009 at 3:38 pm
Thanks so much for your help!
August 6, 2009 at 4:15 pm
Is it the data file or the log file that is growing? If you find that the log file is growing - you need to identify the process that has an open transaction in tempdb:
DBCC OPENTRAN('tempdb')
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2009 at 4:50 pm
It was the tempdb data file...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply