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 service?
Note: 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 ?
A
