What happens Internally when a SQL Server instance is Shutdown and restarted

A very interesting question popped up today: what happens when a SQL Server instance is restarted?

 When an instance of SQL Server is shutdown (i.e. on the way to be restarted) what happens is:

• all databases have checkpoints
• all SQL Server Agent jobs are terminated and the Agent is shut down
• all database files are released to the OS, i.e. SQL Server service releases the locks to the mdf and ldf files (and all other files)
• the memory used by the SQL Server instance is released
• all metadata collected for the DMVs and DMFs is discarded
• an event in the Default trace file is written marking the SQL Server instance shutdown

When an instance of SQL Server is started (i.e. after the service is authenticated and is starting) what happens is:

• The service is authenticated by verifying the credentials provided in the logon account and the service is started.
• the startup parameters of the SQL Server service are read, since they contain information about the location of the master database files and the error log location. The startup string looks like this:

^?View Code T-SQL


-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG; -lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• the port on which the SQL Server is responding are open
• the memory is allocated for use by the SQL Server instance (according to the minimum memory settings of the instance)
• the master database metadata is read – the master database contains metadata about all user databases on the instance and their status
• all user databases are attached to the instance and file lock is acquired on the files
• Undergo database recovery phases (Analysis, redo and undo phases.)
• the tempdb files are allocated according to the settings for their initial size – depending on the security policy settings and the initial size of the tempdb files this may take longer or shorter time; also, keep in mind that the tempdb collation settings are picked up by the model database
• an event in the Default trace is written noting that the instance is started
• all events are written to the SQL Server log – whether there are just info messages or error messages
• connections to the databases are accepted
• metadata is collected about significant events in the DMVs and the DMFs
• Recompile Stored Procedures

References :

http://sqlconcept.com/2011/05/25/what-happens-when-a-sql-server-instance-is-restarted/
http://www.sanssql.com/2012/07/what-happens-when-sql-server-instance.html