#0210-SQL Server-Restart Recovery-Database Startup Sequence


The startup process of any system is a very interesting stage. Ever since I was exposed to the world of computers, I was fascinated with the POST (Power On Self Test) sequence, about which I learnt and explored in detail when I studied Electronics & Communications engineering. SQL Server is a server system on it’s own and therefore, I recently had a yearning for learning about the SQL Server startup sequence.


Now, I know that the SQL Server startup process involves an Undo-Redo phase for rolling forward/backward open transactions in each database, but the questions that kept cropping up in my mind were:



  1. When the SQL Server starts, what is the first database that is made online?
  2. When are user databases made available in the startup sequence?
  3. When is the tempdb available?

In order to answer these questions, I postulated a theory and then tried to collaborate it with practice. The entire experience is documented in this post.


The Theory


When I started learning about SQL Server, I thought that the database_id determines when a database comes online. This would mean that the following DMV would be the only thing tat one needs to be query:

USE master
GO
–Get the database Ids
SELECT sd.database_id, sd.name
FROM sys.databases AS sd
ORDER BY sd.database_id
GO

My test server is a SQL Server 2012 instance with reporting services installed and the AdventureWorks2012 sample database deployed. The result for this test instance is shown below:



































Database Id Database Name Is User Database?
1 master N
2 tempdb N
3 model N
4 msdb N
5 ReportServer$SQL2K12 N
6 ReportServer$SQL2K12TempDB N
7 AdventureWorks2012 Y

The one database that is not listed in the results of this DMV is the resource database.


Going by original hypothesis, the master should be started up first, followed by the resource, tempdb, model, msdb and so on.


The contradiction


Contradicting my thought process mentioned above were some of my pervious observations around tempdb. It is a confirmed point thing that the model database has to be online before the tempdb is made available.



The above contradiction led me to hypothesize that the startup sequence would be similar to master –> resource->msdb –> model –> tempdb –> ReportServer –> ReportServerTempDB –> user databases. It was now time to confirm this line of thought.


The Test


To identify the real order in which databases are brought online, I looked at the SQL Server error log where all startup activity is logged by Microsoft SQL Server. I had referenced this in one of my pervious post related to identification of the startup time of the SQL server. To read the SQL Server error log through T-SQL, we use a system stored procedure sys.sp_readerrorlog (undocumented, to the best of my knowledge).  This system stored procedure takes up to 4 parameters:



  • Error log file to read (0 = Current, 1 = Archive #1, and so on)
  • Log File Type (NULL/1 = SQL Server Error log, 2 = SQL Agent Log)
  • String 1 (first search string)
  • String 2 (string to further refine the search)

We need to read the current file in the SQL Server Error Log, and only need to search for one string – “Starting up”. The call would therefore be similar to the one shown below, where I have captured the results into a table variable which allows us to sort by the log time.

–Check the sequence logged into the SQL Server Error Log
DECLARE @DatabaseStartupSequence TABLE (LogDate DATETIME, ProcessInfo VARCHAR(10), LogText VARCHAR(50))

INSERT INTO @DatabaseStartupSequence (LogDate, ProcessInfo, LogText)
EXEC sys.sp_readerrorlog 0, 1, ‘Starting up database’

SELECT CAST(dss.LogDate AS TIME) AS LogTime, dss.ProcessInfo, dss.LogText
FROM @DatabaseStartupSequence AS dss
ORDER BY dss.LogDate ASC
GO


The results of the query execution are as shown below:







































LogTime ProcessId LogText
15:33:34 spid8s Starting up database ‘master’.
15:34:15 spid9s Starting up database ‘mssqlsystemresource’.
15:34:15 spid17s Starting up database ‘msdb’.
15:34:15 spid18s Starting up database ‘ReportServer$SQL2K12’.
15:34:15 spid20s Starting up database ‘AdventureWorks2012’.
15:34:15 spid19s Starting up database ‘ReportServer$SQL2K12TempDB’.
15:34:19 spid9s Starting up database ‘model’.
15:34:49 spid9s Starting up database ‘tempdb’.

The Result


The above is a very interesting result and tells me that I was not very wrong in my hypothesis. Here’s the interpretation of the log content.



  • We know that recovery is a parallel process – this is confirmed by the fact that we have multiple ProcessIds in the results above
  • The first database is recovered by process Id – 8 and is the master database – without the master database, the entire SQL Server fails to start
  • The next database recovered is by process Id – 9 and is the resource database (“mssqlsystemresource”)

    • This very process goes on to recover, in sequence, the model and then the tempdb database

  • The next process that SQL Server initiated was Id – 17 for recovers the msdb database
  • Process Id – 18 then recovers the ReportServer database followed by Process Id – 19 which recovers the ReportServerTempDB
  • Finally, process Id – 20 recovers the user database – AdventureWorks

The recovery interval (i.e. when the recovery for any database completes) depends on a number of factors and can be studied at: Understanding Recovery Performance in SQL Server.


Conclusion


In conclusion, I could now answer all my questions:



  1. When the SQL Server starts, what is the first database that is made online? Answer: Master
  2. When are user databases made available in the startup sequence? Answer: After all system databases have been recovered
  3. When is the tempdb available? Answer: After the master, resource and model databases are recovered (in that sequence)

I learnt something new today, which is always fun. This topic is by no means complete. There is a lot more to explore, so, if you have any related observations/questions, please do share them via the blog comments, or send them on my Twitter handle (@nakulv_sql).


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0210-SQL Server-Restart Recovery-Database Startup Sequence

Let me know what you think about this post by leaving your feedback here!

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