April 2011 - Posts

Default database location settings are important after all
17 April 11 10:06 AM | GavinPayneUK | with no comments

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!

SQLBits Presentation - Mixing SQL Server and Virtualisation
10 April 11 09:36 PM | GavinPayneUK | with no comments

This Friday I presented a session at SQLBits 8 sharing my advice, experience and guidance about successfully deploying virtualised database servers running SQL Server.

Many thanks to all those who attended and despite my slot being at the end of a long hot sunny day beside the sea I hope all those who attended were glad they did.  Those of you who asked questions during and after the session gave me the impression its a subject you’re collectively very interested in whether you’re experienced or a starter to the topic.  I hope my presentation gives you something to consider in the future one way or another.

Designing a virtual database environment isn’t something my SQLBits presentation alone will allow you to design from scratch however I have recently done day long workshops based the presentation’s content for Coeo clients either looking to deploy new virtual environments or optimise existing virtual infrastructures.  If this is something your business could benefit from then please feel free to email me gavin@coeo.com


Once again, many thanks to all those who attended my session.

The presentation can be found here.