1

I am working with a SQL Server 2005 database, which has the 8 files in 1 filegroup for the TempDB. The initial size of the 1st file is 8MB, and the other 7 are 2GB. This database is a reporting DB, which is populated nightly from an SSIS package. The package and reports use a lot of temp tables.

The files has grown to consume about 300GB, evenly distributed. It is set to grow by 200MB unrestricted. The TempDB is not backed up and is on a SAN.

I have read that you should not use SHRINKDATABASE or SHRINKFILE on TempDB. What is the proper way of performing maintenance in this situation to ensure that we do not max out disk space and keep TempDB lean and mean.

Thanks for any advice and knowledge.

Dustin Laine
  • 433
  • 4
  • 13

1 Answers1

1

Here's article 307487 from Microsoft about just that.

It comes down to a few basic ways:

  1. Restart the SQL instance
  2. Use DBCC SHRINKDATABASE
  3. Use DBCC SHRINKFILE

All of those have their issues as you are aware but I'm not sure of any way to neatly perform this task as you're essentially dealing with one of the most important databases in the instance. If you can afford to shut down the instance for a few minutes that would be the best bet I'd think.

Keep in mind if your tempdb has grown this large in the first place there's a good chance it will get there again. If this is a major issue you should investigate why tempdb is growing so large and plan accordingly. The main reason people cry foul on the shrink operations is because databases tend to grow as big as they need to be unless something is being done wrong. It may not apply to you but it's just a general disclaimer I give out for any questions regarding database shrinking.

Shane
  • 1,869
  • 4
  • 20
  • 34
  • I read that article, and none of these things can be done while online. Is SQL restart the typical way of maintaining this? – Dustin Laine Aug 30 '10 at 20:12
  • SQL restart restores TempDB to it's initial size just because it clears it out of all the temp data. I'd be hard pressed to call any of the methods "typical" and more a case of find which one would work best for your situation. – Shane Aug 30 '10 at 20:20
  • I guess what I am trying to get at is this: Does the tempdb growth need to be looked at or does the shrinking, by restart need to be routine? – Dustin Laine Aug 30 '10 at 20:23
  • Definitely check out the growth. Use sp_spaceused to see how much of that space is not being let go. If your normal operations are causing it to grow to 300GB then that's one thing. If it's unable to release the space then it might be a bigger problem. Tempdb should grow to how big it needs to be and then release that space. The next time it needs to grow it should already have the necessary space allocated. If all it ever does is constantly grow you might have a problem that needs to be remedied before a shrink will do you any real good. – Shane Aug 30 '10 at 20:31
  • When looking at the shrink dialog it states 99% available space. The SP show tempdb 12474.69 MB 4929.66 MB for size and unallocated. But the files are 200 GBs now. – Dustin Laine Aug 30 '10 at 20:38