Tempdb misconfiguration when SQL Server fails to create a secondary data file

  • Article

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.ndfD:\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'.

The Issue

We know that tempdb is configured with 3 data files but just one of them was not created at startup, the other 2 data files were created, but just the .mdf file is used by SQL Server.

DMV io virtual file stats is reporting only statistics for the file_id 1 and file_id 2 (mdf and ldf), this means that even when SQL Server could create the file F:\data\tempdev_sql14.ndf, it's not used by the engine. The reference over F:\data\tempdev_sql14.ndf, was aborted because the service fails to create 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.

Problem resolution

  • 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 )

Twitter @carlos_sfc