How to fix tempdb Full issue ?
1) Since tempdb recovery model is simple , Tempdb log will not grow drastically unless open transactions are there if so we will kill that spid and then shrink it
2) 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)-------user 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 sps to get recompiled
Then perform DBCC shrink file finally which will allows us to shrink it.
1) Since tempdb recovery model is simple , Tempdb log will not grow drastically unless open transactions are there if so we will kill that spid and then shrink it
2) 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)-------user 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 sps to get recompiled
Then perform DBCC shrink file finally which will allows us to shrink it.
