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?
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