I recently added more CPU cores to a SQL Server and as best practices advise, I separated out the tempDB files into the same number of cores(8).
Also along with best practices, I expanded the size of the new tempdb files, limited auto-growth and shrunk the original files to match the size of the new files.
The problem is, since I was going from a single core to 8 cores, all of the tempdb data remained in the first file, which wouldn't allow me to shrink. Now I have one tempdb file that is larger than the others and I would like to change that.
I suspect to fix this it will require an outage.
-
Is there a way to do this without an outage?
-
Is this problem even worth addressing? I know what best practices say, but does this instance really cause any practical problems other than ugliness?
Best Answer
EMPTYFILE is just there is transfer all the pages to other files, so you subsequently can do ALTER DATABASE ... REMOVE FILE. This doesn't seem to be what we want here.
I wouldn't worry about this, just let it sort itself at next re-start. Remember that tempdb is re-created based on what sys.master_files says. This is the "template" for tempdb regarding number, placement, size etc of the files.
You adjust the size in sys.master_files using ALTER DATABASE ... MODIFY FILE. If you want help, just use SSMS, tempdb, properties, and adjust the size for any of the tempdb files to a larger size. Then script the command and adjust to your liking. (The reason to not specify a smaller size in the GUI is that it will generate a DBCC SHRINKFILE, which only (tries) to address the current size for tempdb and does nothing for the template. It is perfectly possible to specify a smaller size than current size using ALTER DATABASE for tempdb as it will only affect the "template" and not the current size.