Saturday, April 21, 2012

Shrink TempDB Data File Without SQL Restart


We can shrink the tempdb data file without restarting the SQL Server using the following steps.

First verify the space usage information from MDF file, If you find there is enough space to shrink then ensure there wont be any open transactions running on the tempdb and execute the below steps to shrink the TempDB Data File.


DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024)
GO

14 comments:

  1. Nice, post. Kalyan, keep up.
    Whoa! Seems Oracle following MS-SQL, in 11g it is possible
    Alter tablespace temp shrink space;
    -Thanks
    Suresh

    ReplyDelete
  2. Thanks, finally worked too

    ReplyDelete
  3. La verdad que sos un genio.
    Gracias!

    -Dario

    ReplyDelete
  4. I have a question that even if we use the above dbcc commands then also it will serve the same purpose of re-starting a sql server as the freeproccache and buffer commands will clear the cache memory and it is moreover same as re-starting any sql server. Hence, how come it became a solution to shrink tempdb without any re-start.
    And I have also got to know that the changes made to the tempdb will only be implemented when it is re-started. Like the shrink done is applied from the next start of a tempdb. Is that true? Please help me out to clear the dust rose.. Thanks in advance

    ReplyDelete
  5. Great, Thanks a lot sir...

    ReplyDelete
  6. Very useful, thank you.

    ReplyDelete
  7. Very Useful, Thank You!!

    ReplyDelete
  8. Awesome .... it really helped me ... :)

    ReplyDelete
  9. EXCELENTE POST!!! me has librado de pedirle permiso a medio mundo para reiniciar el servicio de SQL

    ReplyDelete
  10. Trouble is that we do not have the permission to carry out the task, see error below...

    Msg 7983, Level 14, State 36, Line 9
    User 'guest' does not have permission to run DBCC shrinkfile for database 'tempdb'.

    ReplyDelete