I would like some advice on how to properly resize TEMPDB after a run-away query caused tempdb to autogrow. Fortunately we caught it before we ran out of diskspace, but not before we came dangerously close to running out.
- We have 8x TEMPDB files (tempdevtemp2 thru temp8) initially sized at 4096MB, with 64MB Autogrowth, Unlimited size.
- Recently it grew to 12608MB/file. We have eliminated the scenario that caused the rapid growth.
- Currently our application is consuming less than 1% of TEMPDB.
- We would like to restart the server and have it resized to an initial size of 8192MB/file.
What is the recommended approach to accomplish this?
Ideally, SQL Server restart would bring it up at 8192MB/file. However, I don't see an obvious way to accomplish that, since I can't easily SHRINK it to 8192 and be guaranteed that every file would have that setting before the restart. In the best of worlds, we would have more space available to us, but we are living within our constraints.
Thank you for any assistance.