Sql Server TempDb data file too big
Posted on 2011-02-17
On our production database (Sql Server 2008 R2 64 bit) our temdb is too big for the drive it is on. When we run exec sp_spaceused it says it is using 3 MB of the 40 GB allocated. When we run "dbcc shrinkdatabase(tempdb,100,truncateonly)" it says that there isnt enough unallocated space to shrink the file. When we run "dbcc shrinkfile (tempdev, 100)" it does shrink it by about 1 GB (if it has been a couple days since we last shrank it).
On our dev database (same software) we had the same problem with the same behavior but after we restarted the service, "dbcc shrinkfile (tempdev, 100)" did successfully shrink the file down to 100 MB.
My question is, how can I shrink that file without restarting the service or putting the database in single user mode (since it is our production database we cannot stop it without consequences). If the answer is simply "it isnt possible" then please provide links to documentation of that fact.