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
thank you
ReplyDeleteNice, post. Kalyan, keep up.
ReplyDeleteWhoa! Seems Oracle following MS-SQL, in 11g it is possible
Alter tablespace temp shrink space;
-Thanks
Suresh
Great, thank you
ReplyDeleteWorks! Great... Thank you
ReplyDeleteThanks, finally worked too
ReplyDeleteLa verdad que sos un genio.
ReplyDeleteGracias!
-Dario
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.
ReplyDeleteAnd 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
Great, Thanks a lot sir...
ReplyDeleteVery useful, thank you.
ReplyDeleteThumbs up!
ReplyDeleteVery Useful, Thank You!!
ReplyDeleteAwesome .... it really helped me ... :)
ReplyDeleteEXCELENTE POST!!! me has librado de pedirle permiso a medio mundo para reiniciar el servicio de SQL
ReplyDeleteTrouble is that we do not have the permission to carry out the task, see error below...
ReplyDeleteMsg 7983, Level 14, State 36, Line 9
User 'guest' does not have permission to run DBCC shrinkfile for database 'tempdb'.