T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.

DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

Monitor SQL Server and related services using T-SQL

As part of the DBA job, it is very important to monitor the SQL Server and its related service and ensure that the services are always up and running.
There are different ways to achieve this, and one among them is by using the custom SQL scripts.
Writing custom SQL scripts play a vital role in few environments where budget is a concern to implement an full fledged monitoring system.

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