6

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.

  1. Is there a way to do this without an outage?

  2. Is this problem even worth addressing? I know what best practices say, but does this instance really cause any practical problems other than ugliness?

  • Have you tried using the EMPTYFILE argument of DBCC SHRINKFILE? Docs – Erik Darling May 17 '18 at 15:19
  • I haven't. Was concerned about doing it during production hours. Will it have an impact to users? – Shooter McGavin May 17 '18 at 15:29
  • Do you have scheduled patching in the near future that will include a server restart? – James Jenkins May 17 '18 at 16:13
  • @JamesJenkins. If only it was that simple. We run 24/7. The last couple times I have scheduled an outage, I had to cancel because the business cannot afford to be down, even for an hour. It has been months since our last official outage. Since our infrastructure manager doesn't know how to control Windows updates, it will probably restart on it's own any day now. :) – Shooter McGavin May 17 '18 at 16:21
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.