When you restart Sql Server, it goes through several steps to make sure that the instance is up and running including the user databases. It is important to make sure that the data that was committed before restart is still intact. Rollback and roll forward are some of the steps it goes through for each database but there are several other steps that happen before it even comes to user databases. It is important to understand this sequence to employ better  troubleshooting techniques when faced with fatal issues.

As Sql Server goes through the start up process, ERRORLOG file is updated with its progress. Open ERRORLOG and you get an insight into the start up sequence.

(The sequence below is a simplified version of the start up process for ease of understanding)

• Microsoft Corporation and other machine details
  • These may seem obvious, but they come handy sometimes. The details about the machine on which the instance runs will be helpful.

• Windows process ID
  • Shows the process ID that the Sql Server service is assigned. For OS, Sql is just another service and each service has (along with a lot of other parameters)  a process ID and security context (user account assigned)

• Authentication mode
  • Shows if the instance is enable for mixed mode or just Windows Authentication

• Start up parameters
  • Indicates the file location for ERRORLOG file and master database MDF & LDF files. If you need to move the system database (master), you set the new location here.
    • -dE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;
    • -lE:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;
    • -eE:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Number of CPUs available for this instance

• Amount of memory allocated.

• NUMA configuration
  • If multiple nodes are available they are indicated (node 0, node 1, …) with its CPU Mask.
  • On large systems, you can leverage NUMA configuration to balance the traffic for better performance.

• Now starts master database
  • Master database would be located in the path indicated in the above ‘Start up’ parameter

• Starts Resource governor

• Makes entry about FILESTREAM options

• Starts the default Sql trace
  • By default, this is located at “.\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log”

• Starts mssqlsystemresource database
  • This is a hidden database not available for direct querying. But the objects from this database form the basis for many system catalog views (and other objects)  in user databases.

• Loads self-generated encruption certificate

• IP address and Ports Sql Server is listening on for incomming connections. If configured to accept connections on Named pipes, necessary related information is displayed.

• Starts msdb databases

• Opens up ports to listen for DAC connections.

• Attempts to register SPN (Service Principal Name)  for Sql Server service.
  • For windows Sql Server is a just another ‘service’ and each service runs under a user security context. On start up, Sql tries to register its SPN to be used for Kerberos authentication. If it is not available, it creates an entry that it attempted and failed. This is not a problem, unless your services depend on this. More on this on a separate post.

• Now Sql is ready for incoming client connection requests.

• Starts model and tempdb
  • If there is any incorrect configuration with these databases you can start Sql Server in a ‘minimal configuration’  mode and troubleshoot further.

• Database Mirroring and Service Broker are enabled (if configured)

• Starts user databases
  • Each user database is restored. Any committed transactions are rolled forward  and uncommitted transactions are rolled back.

As you can see, I started this out to give a simplified version, but it evolved into a long short-summary. There are more steps that Sql goes through, but I omitted some of them to keep it simple to get a good glimpse of the overall picture. If you need more detailed understanding, please refer to BoL.

Hope this helps,

_SqlTimes