In MSSQL Server it is possible to fine the date and time the server was started. This can be useful if you have an issue should sql server be crashing or some such and somebody has set the service to automatically restart if it crashed. This could go unnoticed by people except for mayby a random sql disconnect error messages from an application then they appear to work perfectly a minute later.

To get the sql startup time we can use the following.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

The above will give up the sql start date time. However for this we want the time in minutes.

SELECT DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info

Using the above information we can now generate a simple script and send out an email alert when sql server restarts.

DECLARE @TimeFromStart int
SELECT @TimeFromStart=DATEDIFF(MINUTE, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info

IF (@TimeFromStart <= 60)
BEGIN
        EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'example@example.com',
                @body = 'SQL Server Was Restarted!',
                @subject = 'Alert: SQL Server Restarted'
END

You can then add this to a job and set it to run hourly. At this point you will then get email alerts whenever your sql server is restarted.





Last Modified: 19 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-29 - MSSQL - TRIM Function