4

One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is using default values, so if I restart it will go back to 8mb data file growing in 10% chunks.

So at this time there is 8mb free space left on the TempDB drive. Whilst it is in this state, can I set the desired TempDB size of 30GB using ALTER DATABASE commands ready for the next restart, or will the command fail as there is no free space on the drive?

EDIT:-

As per Robert's comments below. This only needs 1 restart as running ALTER DB command on tempdb before restart will cause SQL Server to set tempDB correctly on restart

|improve this question
  • This is also wrong. You don't need to restart tempdb before redefining the size. The GUI attempts do DBCC ShrinkFile. You need to use T-SQL to issue an ALTER DATABASE tempdb MODiFY FILE ..... command. And then just 1 single restart. – Robert L Davis Apr 5 '12 at 14:33
  • Thanks Robert - that's made a lot of sense. Just tested this on a dev box and can use ALTER DB commands to "shrink" tempDB on restart. – mealies Apr 5 '12 at 15:13
  • look up fast file initialization if your worried about initialization times for databases – user8976 May 24 '12 at 19:38
2

By setting Tempdb to be 30GB, you are adding the amount of time required for SQL Server to fully restart. Every time SQL starts, tempdb is recreated. Allocating that much disk space may take longer than desired. I would go with 2GB, but let auto-grow by 64MB at a time.

I would also change the MODEL database to grow by 128MB at a time for data, 64MB for the log. This setting will be applied to all new databases and will prevent the type of internal fragmentation that can happen with too many auto-growths (i.e. the default 10%)

ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=128000KB )
ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=64000KB )
|improve this answer
  • 1
    Thanks for advice about the sizing, will set tempDB to a lower size to reduce restart times. – mealies Apr 5 '12 at 13:36
  • 2
    No no no! This is wrong! TempDB reuses the existing files when it starts up (assuming there are any). It doesn't compeltely recreate the files. Books Online is misleading about that. A 300 GB tempdb will start up in almost the exact same amount of time as a 2 GB tempdb. – Robert L Davis Apr 5 '12 at 14:23
  • @Robert L Davis: You're right, perhaps add that as an answer? We just improved our tempdb from a large collection of 64M chunks to one 100GB file, greatly improving performance. – Andomar Apr 5 '12 at 14:36
  • 1
    Hey, you learn something new everyday here. Please add this as an answer, Robert. I have to wonder though why anyone needs 100GB in a temp file. In all my years as a DBA I have found that in those cases there were some horribly bad queries being executed. Fixed those, no need to waste all that disk space. – datagod Apr 5 '12 at 14:44
1

TempDB reuses the existing files when it starts up (assuming there are any). It doesn't compeltely recreate the files. Books Online is misleading about that. A 300 GB tempdb will start up in almost the exact same amount of time as a 2 GB tempdb.

|improve this answer
  • 2
    Can you add the source for the above? – user4742 Apr 5 '12 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.