#0211-SQL Server-Restart Recovery-Database Startup Sequence for User databases


In my earlier post (Restart Recovery-Database Startup Sequence), I studied the overall database startup sequence as part of the restart recovery process for databases on a SQL Server instance. The conclusion that I came to was that the restart recovery is a multi-threaded activity and multiple spids are utilized to bring the SQL Server instance online in the shortest possible time frame. The first database to come online is (obviously) the master database followed by the rest of the system databases in sequence (resource, model, tempdb, msdb and Reporting Server related DBs). Finally the user databases are brought online.

The question that now came to my mind was that it is possible for an instance to have multiple user databases. What would be the startup sequence for these user databases under normal conditions?

On my test instance, I have 6 user databases according to this script to identify system v/s user databases:

--Provides a list of user databases
select sdb.database_id,
       sdb.name
from sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 0

The user databases on my test instance are listed below:

Database Id Database Name
7 AdventureWorks
8 AdventureWorksDW
9 AdventureWorksLT
10 AdventureWorks2008
11 AdventureWorksDW2008
12 AdventureWorksLT2008

To know the sequence in which these databases were brought online as part of the restart recovery sequence, I used the query from my pervious post with a minor modification in that I am filtering the log text:

--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
WHERE dss.LogText LIKE '%AdventureWorks%'
ORDER BY dss.LogDate ASC
GO

The results are as shown below:

LogTime ProcessInfo LogText
17:41:21 spid24s Starting up database ‘AdventureWorks’.
17:41:21 spid25s Starting up database ‘AdventureWorksDW’.
17:41:21 spid26s Starting up database ‘AdventureWorksLT’.
17:41:21 spid27s Starting up database ‘AdventureWorks2008’.
17:41:21 spid28s Starting up database ‘AdventureWorksDW2008’.
17:41:23 spid24s Starting up database ‘AdventureWorksLT2008’.

Result

Multiple spids are assigned to recover the user databases in order of their database_id. SQL Server started 5 separate processes to recover 5 out of the 6 user database. The 6th database was handled by the first spid that completed it’s work based on whether the scheduler had capacity available.

Database Id Database Name Assigned SPID Remarks
7 AdventureWorks 24  
8 AdventureWorksDW 25  
9 AdventureWorksLT 26  
10 AdventureWorks2008 27  
11 AdventureWorksDW2008 28  
12 AdventureWorksLT2008 24 Rollover, since spid24s would have completed recovery on ‘AdventureWorks’, it was assigned the next database in-line

In conclusion, restart recovery of system databases has a specific sequence that is followed by SQL Server, but when it comes to user databases, the order is governed by the database_id of the database.

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

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