Default database location settings are important after all

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:

DatabaseLocations

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.
20

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!

Advertisements

2 thoughts on “Default database location settings are important after all

  1. Mark Broadbent

    Hi Gavin, I’m sorry but I completely disagree with the message you are conveying here. I understand the point you are attempting to make but to suggest not to change the default locations due to this issue is not imho the best way forward. What I believe the message should be, is to ensure that your default locations (if changed) are maintained as a company standard to set drives/ mount points. Developers (for dev servers) will generally not go to the trouble of specifying file locations and therefore your suggestion is going to cause a big headache to the poor shmuck who has to end up tidying the server up at some point (namely me).

    Reply
    1. gavinpayneuk Post author

      Hi Mark,

      Yes, I fully accept what you’re saying here, in a pro-actively managed environment it can be good to know all your databases will be created in a certain location, and if you have company build and configuration standards then yes I’d agree with you that its a setting you should consider setting away from the defaults. The problem I came across was with a server that had no DBA, no build standards and the person who configured SQL Server many years ago was long gone – very different to the well managed environments we prefer. In those situations I’ll now check the default file location and that they exist before applying a service pack.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s