1

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.

enter image description here

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

enter image description here

How i can shrink 'tempdb' properly ?

A

5
  • try dbcc shrinkfile ('templog',2) and let me know if its reduced to 2 MB or not – MarmiK Mar 14 '19 at 13:32
  • dbcc shrinkfile ('templog',2) this also not reducing the size. – jisto Mar 14 '19 at 13:41
  • 2
    Just because there is "free space" doesn't necessarily mean it can be shrunk. If you are not using mixed extents and all the extents are 50% full (for example), then it's not going to be able to shrink the file even though the amount of data is only 50% of the size of the file. – Tony Hinkle Mar 14 '19 at 14:21
  • @jisto, it sounds like you first need to allocate space on your new drive, with a new tempdb file. You can shrink the old file at your leisure, perhaps after a reboot in your next maintenance window. Note that actually eliminating the first tempdb file for a database is a bit involved. – Jon of All Trades Mar 14 '19 at 14:28
  • Run the "Disk Usage by Table" report on tempDB, and see what is taking up all of the space. You will show all the currenly opened temp tables, as well as any permanent tables in tempDB at the bottom of the report. This will give you some idea what is holding the space. – Laughing Vergil Mar 14 '19 at 17:09