Wednesday, July 25, 2012

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,

Checkpoint is issued on all databases

Check for the jobs that are running and stop
them

Release the locks on database files to Operating
System

Release the memory used by SQL Server instance

Flush the metadata collected for DMV’s and DMF’s

Record an event in default trace and event
viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service,

The service is authenticated by verifying the credentials
provided in the logon account and the service is started.