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:

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:

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.

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.