Tuesday, November 8, 2011

0 Shrink Tempdb

Tempdb is playing more heavily role since SQL Server 2005. if we did not control or monitor it well, it may grow and eventually will fill up the disk.
Tempdb may not be able to shrink if there is an internal object still use tempdb. Per Kalen Delaney suggests in the “Microsoft SQL Server 2008 Internals”, it is better shrink the individual file instead of shrink the tempdb as a whole. She also suggests in the book that it is better  to use alter database to change the tempdb size and bounce the MSSQL.
  1.   ALTER DATABASE tempdb MODIFY FILE
  2.    (NAME = 'tempdev', SIZE = target_size_in_MB)
The Alter database wont change the current tempdb size but only change its initial size. The tempdb will be recreated as the initial size after Server bounce.
We can use below query to find out What is the current size of the tempdb and its used space.
  1.     use tempdb;
  2.     SELECT df.name ,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,df.size/128.0 as CurrenSizeinMB,mf.size*8192/1024 AS InitialSizeinKB
  3.     FROM sys.database_files df join
  4.     sys.master_files mf on df.name = mf.name and database_id = DB_ID()
  5.     go
image
  • Tempdb ‘s db id is always 2
  • sys.master_files store the database file last configure size
  • Tempdb will be recreated every time base on its last configure size (sys.master_files)
In my experience, if you have luxury to bounce the SQL server, you might just want to make sure its initial size or use alter db to change initial size then bounce, in stead of go through the single user mode suggest here http://support.microsoft.com/kb/307487
If you do not have luxury for bounce SQL Server. you can try
  1. DBCC FREEPROCCACHE
  2. GO
  3. USE [tempdb]
  4. GO
  5. DBCC SHRINKFILE ('tempdev' , sizeInMB)
  6. GO

To remove the tempdb file , we can use

ALTER DATABASE tempdb REMOVE FILE tempdev2

In most case, you will get the error message like "The file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSGU1\MSSQL\DATA\tempdev2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.
"
After restart the SQL server, the tempdb file is being removed.

Reference

How to shrink the tempdb database in SQL Server http://support.microsoft.com/kb/307487

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates