When most of us create databases we explicitly set the file locations in the create statement, either directly through the T-SQL or the SSMS interface. And that’s a really good practice, datafile placement is a decision that can affect the database’s performance for the rest of its life if made badly.
SQL Server attempts to reduce the overhead of remembering where you want your databases kept by having a “database default locations” setting in SSMS, as pictured below:
This default setting allows a simple create database statement to put the datafiles where we’d like them, however, most of us never use these settings, we always specify our datafile locations but that doesn’t mean the setting is irrelevant.
Should someone choose to change that path to a non-default setting you need to make sure that directory continues to exist. During a complex server deployment its common for drives to be added, removed or swapped about, especially if you’re waiting for the delivery of longer lead time storage components
Temporary Service Pack Databases
During a recent build I learnt that applying a SQL Server service pack or cumulative update involves SQL Server creating its own temporary database “temp_MS_AgentSigningCertificate_database” and the creation of this database uses the database default locations setting. This is fine and transparent to most of us as the default value as the database default locations setting is the “home” folder of the SQL Server instance.
Cannot create database
But, if the database default location has been changed to a directory which no longer exists while you may not notice in everyday server usage you will notice when you apply a service pack or CU. Only after SQL Server doesn’t re-start and fails to start will you find the cause in the error log:
2011-04-15 18:40:07.85 spid7s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file 'd:\temp_MS_AgentSigningCertificate_database.mdf'.
2011-04-15 18:40:07.86 spid7s Error: 5123, Severity: 16, State: 1.
2011-04-15 18:40:07.86 spid7s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'd:\temp_MS_AgentSigningCertificate_database.mdf'.
2011-04-15 18:40:07.86 spid7s Error: 1802, Severity: 16, State: 4.
2011-04-15 18:40:07.86 spid7s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2011-04-15 18:40:07.87 spid7s Error: 912, Severity: 21, State: 2.
2011-04-15 18:40:07.87 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2011-04-15 18:40:07.87 spid7s Error: 3417, Severity: 21, State: 3.
2011-04-15 18:40:07.87 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
The cause of this as I found was that the database default location had been changed to a drive which no longer existed. When the service pack tried creating the temporary database it couldn’t so failed. As a result whenever SQL Server started it was still in upgrade mode but couldn’t complete the upgrade scripts.
The easiest way to solve this problem was to re-create the path it couldn’t find, changing a drive letter in Windows for 5 minutes, then once the service pack was finished, I changed the drive letter back, and the database default locations.
moral of the story
The moral of this story is not to feel tempted to change the default location settings. As long as we specify the data file locations when we create databases we don’t need them anyway and if they are changed the chances are we’ll only find out if it was a bad change at the worst moment!