MSSQL - Checking Uptime

24. September 2011 18:00

 

In MSSQL Server it is posisble 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.

E-mail Kick it! DZone it! del.icio.us Permalink