December 29, 2008 at 10:39 am
Hi all, I'm experiencing a problem with TempDb in a production machine:
Microsoft SQL Server 2005 - 9.00.2153.00 (X64) Standard Edition (64-bit) on Windows 2003 server sp2
We have a production database of 90 gb and the recovery level is FULL.
Recently, during the night, TempDb increased the dimensions (30 gb or more) till it finished completely free space on the disk and this caused several problems: usually the dimension increase was progressive day by day and never happened this in one night.
I checked the sql server log, sql agent log and the windows event viewer but I didn't find anything useful.
The fact is that during the night we're running several jobs (with reads and updates) so I cannot detemine which one is causing this.
There's a way to discover what is causing this after days?
Is there a log of the Tempdb dimensions and the processes that increased it?
Thanks for any help.
Lorenzo Ascione
December 29, 2008 at 11:30 am
...
There's a way to discover what is causing this after days?
Is there a log of the Tempdb dimensions and the processes that increased it?
There isn't a way unless you plan for it. You should normally monitor "autogrows" which although helpful should only be used as a means to let you know that something abnormal (unexpected) happened. We normally setup alerts and traces around these conditions. Such grows tend to happen for many reasons but the most common ones I have seen are excessive amount of data produced by crossjoining large tables (due to bad queries) and very concurrent index maintenance activity.
You should set that up immediately.
* Noel
December 29, 2008 at 12:19 pm
The default trace that is running logs autogrow events (EventClass 92 - Data File and 93 - Log File). You can query that using fn_trace_gettable. This will tell you when the growth happened, but not what caused it. You would either need a second trace running or you can look at jobs that are scheduled at that time if you think that may be the cause of the growth. If you do run a second trace you can use the EventSequence to see what happened immediately before the growth.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2008 at 10:37 pm
Hi Lorenzo,
There is no straight forward answer to this as But below are some point which you might need to look at.
Do you have...
1). Many operations being done with out any commits
2). Many separate processes running asynchronously
3). Are the processes all in separate transactions?
4). or one massive process without commits?
Below links might be helpful to you
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
http://www.mssqltips.com/tip.asp?tip=1432
http://support.microsoft.com/default.aspx/kb/307487
What was the last time the sql server service was restarted, Just wanna know how long it took to grow to 30 GB.
Hope this Helps...!!!
Rohit
December 30, 2008 at 1:04 am
Hi Lorenzo Ascione
You need to schedual defragmentation of tables and indexes by running DBCC commands you can reduce the sacan density of table and also index defragmentation.
and also shrink the tempdb.
Regards,
Yousaf Khan
December 30, 2008 at 1:11 am
Yousaf Khan (12/30/2008)
Hi Lorenzo AscioneYou need to schedual defragmentation of tables and indexes by running DBCC commands you can reduce the sacan density of table and also index defragmentation.
and also shrink the tempdb.
Regards,
Yousaf Khan
None of this should need to be done to tempdb. You mention many nightly jobs setup. I'd start by looking at the autogrows in recorded in the default trace (as I mention in my first post), and then look at jobs that are scheduled to run around those times. It would appear that you have at least one job that is using a lot of tempdb resources. A reindex, index defrag, or just some process that uses large cursors/temp tables/table variables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2009 at 2:10 pm
Thanks all for your reply, but till now we didn't find anything.
We changed the schedule of the jobs and seems to solve but we're not sure at 100%.
Lorenzo Ascione
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy