Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
show 3 more comments

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.