One of the many functions and responsibilities of a Database Administrator is to keep up with technology. The frequency and number of required patches, updates and fixes to install on SQL Servers can be quite overwhelming. It's a tedious job; it takes time and preparation to keep all SQL Server instances up to day, especially if you are responsible for maintaining different environments.
Applying patches or updates to SQL Server instances is not rocket science, but the time I applied an update to one of my servers gave me an experience that I would never forget. I downloaded and applied Cumulative Update 4 (CU4) on a SQL Server 2008 R8 SP1 instance. The installation ran and finished without any errors. However, when I tried to connect to the instance I could not log in and all SQL Server services were stopped.
I tried to start the SQL Server Engine and SQL Agent and both services started without a glitch. However, when I tried to connect to the SQL Server instance through Microsoft SQL Server Management Studio, I got an error message. The error message stated that only the “sa” account could login. I opened the error log file for SQL Server which in my server was located in the C:\Program Files\Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\Log directory. I scanned through the error lines finding the entries displayed in Figure 1.
Figure 1
I read the lines at the bottom of the log file (Figure 1) and focused on the last line “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuilt it. For more information about how to rebuild the master database, see SQL Server Books Online.” The message seemed to me that the master database was inaccessible or corrupted.
I continued checking the error log file and read previous error lines finding additional information about the master database file. In Figure 1, some of the errors state “Directory lookup for the file “E:\SQL Server\Data\temp_MS_ AgentSigning Certificate_database.mdf” failed with the operating system error 3 (The system cannot find the path specified).” This error message stated that the SQL Server instance is looking for the certificate database and cannot find it.
When the CU4 setup package runs on a SQL Server instance, the setup program creates a temporary mdf file to get a certificate using the SQL Server instance’s default database directory. The setup program tries to save this temporary file in this directory. In this case, the directory “E:\SQLServer\Data” for the SQL Server instance didn’t exist causing the CU4 setup program to fail the installation.
I’m not really sure why the SQL Server default directory was set to “E:\SQLServer\Data”; however, to fix the problem I created the directory “E:\SQLServer\Data” and restarted the server. After rebooting it, all SQL Server services were running properly and I was able to log into Microsoft Management Studio without a problem using my domain account.
To make sure this issue didn’t occur again, I modified the database default directory to an existing directory. I logged into SSMS and right clicked on the instance name to get to the properties option. Under the “Database Setting” option, I changed the “Database default locations” data and log values (see Figure 2) and used “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data” as the default directory.
Figure 2
I didn’t have to rerun the CU4 on the server, it completed successfully after restarting the server and changing the database default location. All activities went back to normal on the server, and for me I can say it was a very interesting experience.
I hope this small article helps you out if you ever encounter this issue. Here's another great reference: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx
