First published on MSDN on Nov 10, 2016
One of the tempdb best practices when you configure SQL Server is to create additional data files for tempdb according the number of logical processor per NUMA node up to eight. Even with SQL Server 2016 you can specify the creation of those secondary datafiles at installation time. Well, a few days ago, reviewing a customer environment I found something curious, look at this screenshots:
As you can see sys.master_files is reporting 3 datafiles for tempdb + 1 logfile meanwhile sys.database_files is showing up only 1 datafile + 1 logfile.
When the SQL Server service is starting initializes tempdb, this means that deletes old files and create new ones, if you look at the errorlog you can see:
Starting up database 'tempdb'.
Then I looked for the files:
Again, looking at the error log we can find:
2016-10-22 00:00:51.810 spid10s Error: 5123, Severity: 16, State: 1. 2016-10-22 00:00:51.810 spid10s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'd:\tempdb2\tempdev100.ndf'.
ALTER DATABASE [tempdb] REMOVE FILE tempdevsql100 GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdevsql100', FILENAME = N'F:\data\tempdev100.ndf' , SIZE = 256MB , FILEGROWTH = 64MB )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.