While checking my
tempdb details, its showing that almost 90 GB is consumed. So I tried clearing all caches and perform shrinking, but still it's consuming 90 GB of space.
My drive became full where tempdb is allocated, which is why I am trying to get space from database files.
I have executed following queries to resolve issue. But it is still consuming 90 GB.
DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers DBCC FREESYSTEMCACHE ('ALL') -- clean system cache DBCC FREESESSIONCACHE -- clean session cache DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb dbcc shrinkfile ('tempdev') -- shrink db file dbcc shrinkfile ('templog') -- shrink log file
The tempdb database has only one .mdf and one .ldf file.
Is there any option to clear
tempdb without restarting
MSSQL Version 2012,
tempdb is in simple recovery mode.
Update : While checking the Messages of shrinking It is showing 'DBCC SHRINKFILE: Page 1:12185400 could not be moved because it is a work table page.' I hope its due active connection to tempdb, So i checked sp_whoisactive its not showing any connection. But replication is going on the server
Also i got table wise size from tempdb as follows
How i can shrink 'tempdb' properly ?