1

This question already has an answer here:

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

marked as duplicate by Josh Darnell, Randi Vertongen, John Eisbrener, Paul White sql-server Mar 14 at 18:06

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • try dbcc shrinkfile ('templog',2) and let me know if its reduced to 2 MB or not – MarmiK Mar 14 at 13:32
  • dbcc shrinkfile ('templog',2) this also not reducing the size. – jisto Mar 14 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 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 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 at 17:09