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.
asked Oct 05 '12 at 08:13 AM in Default
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.
answered Oct 05 '12 at 10:27 AM
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...
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.
answered Oct 05 '12 at 08:50 AM