Disk space finished by TempDb

Question

Disk space finished by TempDb

aslorenzo

SSC-Addicted

Points: 483
More actions
December 29, 2008 at 10:39 am

#203380

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

...
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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

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

Ramji29 Hall of Fame Points: 3832 More actions · Answer 3

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

Yousaf Khan Ten Centuries Points: 1149 More actions · Answer 4

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

Yousaf Khan (12/30/2008)
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

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

aslorenzo SSC-Addicted Points: 483 More actions · Answer 6

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