I have a case where i'm running into an issue with the tempdb not being large enough. Here is my error:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The tempDB spans 16 files each with an initial size of 4096 MB and Autogrowth is set to none. I have 118 GB free space on the SSD's
I want to turn Autogrowth on however this is in production. Does SQL need a restart to pickup the new setting?
Yes the executing stored procedure will be investigated to see why it's storing so much data in table variables.
Best Answer
As far as a restart goes, no you don't need to. However there's several things to think about before growing TempDB, some of the main ones are:
-Resize all of them to the same size. TempDB uses a round robin proporitional fill algorithm which will create hotspots on physical files which are smaller in size. Paul Randal goes through how to properly do this here.
-There's logical and physical contention. Logical usually arises when you have to few physical files to spread the IO requests too. Check your metrics to see how fast your current disks are performing and benchmark it again after you get new disks. Then you'll have an idea what your current workload threshold is. Review the DMVs to use here. Read more about detecting logical contention here.
If you don't care about all this nit picky performance info and this is a environment where root cause analysis, very high uptime, perf. expectations are not critical; you could just throw hardware at it but make sure you review the info around tempdb. Edit: Aaron Bertrand gave some great things to check in the of another answer here, which I will copy here: You should set trace flag 1117 globally but also add it to the startup parameters so it is persisted.