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?
Sql-server – Chaning tempdb settings on production database
database-sizesql-server-2012tempdb
Related Question
- Sql-server – SQL Server TempDB Growth issue
- Sql-server – Tempdb files don’t exist
- Sql-server – IO request time and less write latency
- Sql-server – Tempdb change settings and recommendation
- Sql-server – TempDB contentions
- Sql-server – Understanding the TempDB growth
- Sql-server – TempDB file overflow
- Sql-server – MSSQL TempDb full
Best Answer
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.