Moving Tempdb AND changing Initial size at the same time.

Question

Fellow DBa's

I need to move tempdb and reset the inital size - my growth is fine. Obviously both of these require a restart.

My questions are:

1 - Can I do these all in the same batch of commands, like below, and do one restart. Or do one set (location change), restart and then another set (initial size) and restart.
2 - Does it matter which goes first: init and location move or location move and then init.

Was planning to to the below in this exact order with just one reboot for all of it.

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'M:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'M:\MSSQL\DATA\templog.ldf');
GO

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = 1024)

GO

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = 1024)

GO

thx MG

Answer 1

Fellow DBAs
I have completed the test for the tempdb scenario.

yes, for TempDB, you can run your size change script in the same code along with the location change script. Then restart. It works fine.

But I did uncover a scenario that explains why some say that when you change the tempdb initial size you have to restart sql and others say you do not.

I was testing with SQL 2016

1 - You want to increase your tempdb mdf/ndf files to be consistent. You change the size with the guid to be the same. Look at the script. It is simply using Alter Database...... size... ). That did NOT need a restart.

2 - But, if you want to reduce the intial size, like in my situation, it is different. Lets say your multi tempdb files were never aligned right - some where 5 gb, some where 1 gb and you saw lots of free space on the larger ones. Thus you might choose to take initial size down. I wanted 1024 mb. If you do this in the gui, and then generate the sql script, you will find a shrinkfile command with your new size. That is fine but if tempdb is in use, you likely will not be able to get the size down. That is also what happended to me in the beginning. Actually some of the files did shrink a tad but no where near enough. In that scenario, you have to use the Alter Database ..... Size.... command. Then do a restart.

Thx

MG

Answer 2

Hi MarkGordon-2676,

Some things got out of limits and I am taking the size down across my tempdbs.

Could you please check the used space of tempdb?

USE tempdb;  
Go  
SELECT Name , Size/128.0 AS Size , FILEPROPERTY(Name , 'SpaceUsed') /128.0 AS UsedSpaceMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB   
FROM sys.database_files;   

Please check if the initial size you want to set is greater than the used size when the sql server is not restarted.

Please refer to this method from this article which might help:

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Mark Gordon ,

as far as I know increasing the temp db size does not require a restart of the SQL service.
Other resources in the internet saying you need to restart the SQL service after resizing the tempdb.

I would recommend to resize the tempdb first, move the tempdb and restart SQL service at the end.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 4

I would find a throwaway instance somewhere and test. VMs are great for this. Create a snapshot before you test, so that you can roll back if things go south unexpectedly.

My gut feeling is that you should be able to modify all in one go, but test before you take it to production.

Answer 5

Hi @Mark Gordon ,

reducing/increasing the size of the tempdb should be the same way.

The approach of Erland is the best option to give it a try.
Resize tempdb, move tempdb and restart SQL service on a test SQL instance

If this works:

Create a snapshot before doing anything with the production SQL Server
Resize, move and restart SQL service on a production SQL instance

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten