Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for production I would like to be sure everything works properly. Some are stating conditions, not always necessary, sometimes necessary, I am reading resources below:
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0
Do You Need an Outage to Add tempdb files? Brent Ozar https://www.brentozar.com/blitz/tempdb-data-files/ "Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added."
http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/ "Once the additional files have been created, it’s not always necessary to perform a service restart. However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first. "
The following resources are saying no restart required: https://www.codykonior.com/2015/08/10/modifying-tempdb-database-files-without-a-restart/ http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/