question

MarkGordon-2676 avatar image
0 Votes"
MarkGordon-2676 asked MarkGordon-2676 commented

Moving Tempdb AND changing Initial size at the same time.

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MarkGordon-2676 avatar image
0 Votes"
MarkGordon-2676 answered MarkGordon-2676 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarkGordon-2676 , the shrinkfile serves no purpose in your case because the restart will recreate tempdb with the new initial size and with the new file locations. Since you're reducing the size, both the size and location changes are only to meta-data and deferred until the restart. Don't forget to delete the old tempdb files afterwards.

If you were increasing the size, that would happen immediately to the current files regardless of the order in your script but the location changes still require a restart. It might be better to change the location, restart (with the smaller sizes), and then increase the size in that case.

0 Votes 0 ·

HI Dan,

Thanks for the info and comments.
Yes, I was saying the same thing. What I was trying to explain is that there are numerous postings where some people say you should restart temdb and other say that you do not after iinitial size change. It depends on the direction - up or down. And, the fact that the gui is showing you a shrink command if you try to reduce it manually with the gui - is interesting. In my case I would be doing a alter database command as the only option. and will not be doing a shrink.

thx again
MG

0 Votes 0 ·
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered MarkGordon-2676 edited

Hi @MarkGordon-2676 ,

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI Andreas,

I am reducing the init size. Some things got out of limits and I am taking the size down across my temdbs. I also tried shrinking them down and could not get them to budge enough.
MG

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered MarkGordon-2676 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered MarkGordon-2676 commented

Hi @MarkGordon-2676 ,

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered RobbieVarn-7255 commented

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:
117863-image.png

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.



image.png (73.4 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Amelia, Thx but the question is can you do both at one time with ONE restart

Initial size change script AND new tempdb location script.

Mg

0 Votes 0 ·

Yes, you should be able to run your commands with only one sql restart for them to take effect.

0 Votes 0 ·