x

Temp db size drastically increased

Hello we are using sql server standard edition sp4 on our production. yeasterday in monitoring i found the database size of tempdb is 1 gb but in the morning today i found a drastical increase in the size of 5 gb. i am not able to find what was the reason for that. can any one suggest how to reduce its size. and if we reduce will it effect the data in other databases please suggest how to proceed now as the disk space is also getting lower with that huge growth.
more ▼

asked Oct 05, 2012 at 08:13 AM in Default

sand143eep gravatar image

sand143eep
811 26 37 41

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

TempDB is always recreated when SQL Server is restarted. For TempDB to grow 400% over night is due to an oparation that utilized TempDB, this could be a user transaction, a massive sort operation, an index optimization with sort in TempDB. As @Tomas Ruston mentioned, TempDB should be sized properly as well as configured to best practice. How many data files do you have in comparison to CPU cores. The rule of thumb is 1 per core up to 8 cores, if you have more than 8 cores then start with 8 equally sized files. So if you have a 4 core machine and your TempDB naturally grows to 4 GB with normal operation, then I would create 4 1.25 GB data files.

Shrinking TempDB can cause it to corrupt. If you were to look at the space used within your tempdb you would probably notice that most of the database is free space. It grew due to the transaction and when it completed that space was freed up. As long as the expanded space is not causing harm, there is no need to rush to reset SQL Services to resturn it to a smaller size.

Turning on Instant File Initialization is also a good idea so when TempDB grows or is recreated when SQL is restarted that you do not have to wait for it so zero out the pages in the data files.

If TempDB isn't already on a separate set of disks, that should also be a consideration.

Just my 2 cents.
more ▼

answered Oct 05, 2012 at 10:27 AM

Tim gravatar image

Tim
36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left

If, as @Fatherjack suggests, there's nothing unusual going on, I would recommend not only not shrinking the tempdb, but also changing the initial size of tempdb to something just beyond its current size (say, 6GB) and changing its autogrowth settings to grow at a rate of, say, 1GB at a time when required.

You say that you're running short of disk space for that database - can it be moved to another drive? Or can you clear some other data off the drive that hosts tempdb? NTFS starts getting a bit slower if there's less than 10% free disk space...
more ▼

answered Oct 05, 2012 at 09:29 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left

something that was processed between your two checks needed a lot of space in TempDB so it grew to accommodate that process. If nothing unusual was taking place then I'd suggest that the database isnt reduced as it will just grow again to accommodate the process at the next execution.

There are ways to locate usage of TembDB from TSQL, dmvs, Execution Plans etc etc.

I think I'd use sp___whoisactive from Adam Machanic - http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

Use it to log results to a table and run it every few seconds over the period that you want to monitor and then analyse the results afterwards.
more ▼

answered Oct 05, 2012 at 08:50 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

if i restart the sql server then i think the tempdb space will be reduced. if i do so are there any problems apart from loosing the history of logs. is there any way to take backup of logs before restarting the sql server.
Oct 05, 2012 at 09:22 AM sand143eep
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949

asked: Oct 05, 2012 at 08:13 AM

Seen: 787 times

Last Updated: Oct 05, 2012 at 10:27 AM