Let's go through a few points.
1- Why Even Shrink? What Are The Benefits And Cons?
First is the 700GB extra space causing any issues? If the DB is already at 2.3TBs, it will probably continue to grow, if so, then consider just leaving the space. In fact, you WANT free space in your DB if it's still growing! You don't want it to expand often, as that causes physical file fragmentation and causes blocking/performance issues when it's growing unless you have Instant File Init. enabled, then the blocking/locking issues are largely negated.
When you take backups those free 700GB are not actually copied in the backup, just pointers to empty pages which will then be populated on restores, thus doing this will not reduce backup time, restore time, backup space, but will take up restore space. The only time it'll affect restore time is if you do not have instant file initialization enabled and your SQL Server Service user is not an administrator.
Reindexing again will increase the free space for it to hold temp info and such, but again, if you end up growing and using that free space then there's no problem.
Perhaps if you are restoring to a smaller dev server that doesn't have as much space, then this would be warrented.
2-To Shrink Or Not To Shrink
Now assuming you absolutely need to reduce your DB size, then read Paul Randal's post on moving to a different filegroup instead of shrinking. This ensures that your shrink does not create additional free space. If you have a clustered key it also reorders the base clustered index as well.
Note: this post might be useful too:
Issues with TempDB mdf file ever increasing
Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb.
A different question has dealt with figuring this out for #temp tables; I don't know if it can be adapted for work tables:
Find which session is holding which temporary table
I've also blogged about it (again, for #temp tables):
http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table
I doubt the work table is related to snapshot isolation / version store, but just in case:
Find transactions that are filling up the version store
Also, don't rely on DBCC OPENTRAN;
- I've observed many scenarios where I know I have an active transaction but it doesn't show up there. And note that database context is important; the database where the transaction is active is not necessarily tempdb. What do you see here? Anything?
SELECT * FROM sys.dm_tran_active_transactions
WHERE name = N'worktable';
Once you've shrunk tempdb
Of course, this isn't a permanent solution. You're going to shrink tempdb, and then it's going to grow again. This can become very boring and tedious to play this game every time it happens. And if it is going to grow again, what are you going to do with that free space in the meantime? Lease it out and then evict people when tempdb needs it again? You need to either:
- Fix the process that is making tempdb grow abnormally large in the first place.
- Allocate enough space for tempdb so that it doesn't need to grow, and stop shrinking it (especially if only temporarily; this is just wasted work!).
A couple of other suggestions:
- Don't use
SHRINKDATABASE
(which should be called auto-fragment) or the UI. Write specific, targeted SHRINKFILE
commands to affect individual files.
- Consider using multiple files for tempdb (which you could spread out to different storage if/when possible), and consider trace flags 1117 (as long as this behavior will not affect your user databases too) and 1118.
- Some tips on minimizing tempdb utilization here:
Best Answer
Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Why Does the Transaction Log Keep Growing or Run Out of Space?
Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.
If you want to shrink the data file by 10GB, look at the size it is now, and then use
DBCC SHRINKFILE
(notDBCC SHRINKDATABASE
) and set a size 10GB smaller than that. Look at thesize
column fromsp_helpfile
- let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).