Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile has grown from ~5GB to ~30GB.

This is causing some space issues on the drive that it is located on. The server is Windows 2003 and we are running SQL 2005.

This was a one off event and so we don't want the TempDB file to stay that large. I have tried running DBCC SHRINKDATABASE and DBCC SHRINKFILE commands however it says:

Page 1:571344 could not be moved because it is a work table page.

I saw this question TempDB data files don't shrink well on SQL 2008 and was wondering if I need to free all system cache before I can shrink the database down. Obviously I don't want to do that on a live database if I can help it.

Is there any other way that I can shrink TempDB's MDF file?

share|improve this question
Thanks @ThomasStringer. From what I understand based on that link there is no way to shrink the file while the system is running. Is that correct? Is there any work around for that? – Greg Jun 4 '13 at 1:52
Do you really need the disk space back immediately and before the next time you can take a maintenance window? Why arguments were you supplying to the DBCC commands? Maybe try shrink file with something more reasonable than 1 (which is what I typically see). Also don't use DBCC SHRINKDATABASE. – Aaron Bertrand Jun 4 '13 at 1:54
@AaronBertrand it can wait - we just wanted to check if that was the only option. We have tried shrinking the file from 30GB down to 20GB but till get the same error. DBCC SHRINKFILE (name = 'tempdev', size = 20000) – Greg Jun 4 '13 at 1:57
Might try smaller chunks at a time. Also a work table should normally be a problem only if a transaction that uses it is still active. What does DBCC OPENTRAN say? (You may need to check user databases as opposed to tempdb.) – Aaron Bertrand Jun 4 '13 at 2:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.