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
Best Answer
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%)