What happens when a SQL Server instance is restarted?

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service, 

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

Posted by Principal at Thursday, August 02, 2012