Wednesday, April 13, 2011

‘temp’ database growing huge

I am not going to discuss reasons why temp DB grows huge but will make a note how to reduce its size.

Basically, a temp database holds all the temporary data. It is used to perform sort operations, used by cursors to store temporary data, it contains all temporary tables.

As part of an immediate resolution, we generally prefer to create secondary log/data files in other drives where we have enough free space this provides us at least minimal amount of time frame to decide the course of action applicable.

We can use below 3 methods to reduce the size of tempdb:

SQL Server restart

By performing an sql restart or DBCC SHRINKDATABASE the tempdb size will be reset but it will not reduce below the last configured state. Last configured state is nothing but the file size set by using either the ALTER DATABASE with MODIFY option or the file size changed using DBCC SHRINKFILE. So we have to understand that the file size of tempdb will not come below this size.

By using DBCC SHRINKFILE we can reduce the file size of any database to desired value even we can shrink below the last configured state which is not possible using SHRINKDATABASE command. The only limitation is, we can’t shrink data/log file size smaller than the size of MODEL database size. So here also model database act as a base. After restart temp DB will be recreated by using model DB as a template.

A temp DB is configured by default to truncate log file when check pointer occurs.

Microsoft kb article:

No comments:

Post a Comment