To Shrink TempDB in Sql Server below ideas can help you to handle the issue.
If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command.
1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are:
(i)check if any locks are there through (sys.dm_tran_locks)or sp_locks
(ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance
(iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE which will remove all the cached objects from Proc cache. But from development perspective this will lead to spaces to get recompiled.
Then perform DBCC shrink file finally which will allows us to shrink it.
If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command.
1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are:
(i)check if any locks are there through (sys.dm_tran_locks)or sp_locks
(ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance
(iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE which will remove all the cached objects from Proc cache. But from development perspective this will lead to spaces to get recompiled.
Then perform DBCC shrink file finally which will allows us to shrink it.
Comments
Post a Comment
Please avoid link comments