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:
| F:\data\tempdev_sql14.ndf D:\tempdb2\tempdev100.ndf |
 |
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'.
Facts
- At startup when SQL Server fails to create an additional data file (.ndf), if single error occurs SQL Server removes the reference to the all additional data files (.ndf) but it's able to start tempdb with the minimal configuration .mdf and .ldf. This can follow to performance issues and tempdb contention.
- At startup when SQL Server fails to create the .mdf or the .ldf a major error occurs and the instance is shutted down.
- Validate the path and permissions of the SQL Server service account to create the file 'd:\tempdb2\tempdev100.ndf'
- Or simply remove the logical file and then create an additional data file in a valid location.
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 )