I have to change the initial size and autogrowth of my tempdb on production. Along, with that I have to add new files to tempdb. What is the right way to do it? As it is production and I don't want to take any risk. Can I directly go to temdb properties and can change the settings/T-SQL or do I have to consider other things also?
asked May 15, 2015 at 14:57
-
1You can't do this without an outage - you'll need to restart for the settings to take effect, so you'll want to save it for a maintenance window or other planned downtime. Just document exactly what you're going to do, make sure you have buy-in from multiple people that the right settings are being applied, and have an extra set of eyes overseeing the actual implementation. The only thing worse than correcting ill-advised settings is having to correct them twice.– Aaron BertrandCommented May 15, 2015 at 15:10
-
@aaronbertrand That's a great advice. +1. I am planning to do it in non business hour and also I will restart the server.– ZerotoinfinityCommented May 15, 2015 at 16:40
Add a comment
|
2 Answers
Sure, you can do it with little risk, but it depends on the state of your current tempdb. If tempdb has grown quite large, you might need to shrink it first before you have enough space for perhaps 4 good size tempdb files.
Because of how active tempdb is and how hard it is to shrink that you might need to schedule a little downtime to make the clean change with all files of exactly the same size.
See a post outlining a few issues at: http://www.brentozar.com/blitz/tempdb-data-files/
Notice that there are a couple of subtle decisions to make. In the sample scripts it describes four 8 GB files, but the sizing naturally needs to support your space requirements. Consider whether tempdb auto-growth should be allowed; Brent recommends correct sizing with no auto-growth.
-
So, I will first reset values and add new files. Then I will restart my SQL Server engine. Will that is fine? I was bit worried about data corruption. So, I was thinking to do in this way 'Restart > change setting > restart. Commented May 15, 2015 at 16:42
You can go directly to the tempdb properties and change the makeup of the database like you would any other database. The difference here is that the changes you make will reset the initial creation size when SQL Server starts up.
It will also affect the current tempdb database that is live. So if you add more files in properties, it will actually add the files right now and set the size to be what you wanted.
The other thing to note, is that you should have the files be all the same sizes and look into using trace flag 1117. It is not required, but this trace flag makes all the files that autogrow, grow together in the same increments.
Just know that if the tempdb has autogrown, when SQL is restarted it will recreate itself to the sizes that you put in the properties, not to the autogrown sizes.
