Check SQL Server Uptime
-
Hi Board,
Do anyone know if there's a easy way to check the uptime of the sql server and downtime and the logs for the downtime?
Thanks lot in advance.
Daniel.
-
Before SQL Servers stops completely it will log a message both in to SQL Server Error log and OS Eventlogs, Works the same way when you start the sql server. Check you Errorlog you will see " SQL Server is starting at priority class 'XYZ'( # CPUs detected)" and "SQL Server is terminating due to 'stop' request from XYZ". You can also see the Date/Time and the source
Shas3
-
Thanks Shas3. But what I'm looking for is something that will let me generate a report for the length of the uptime, downtime, and the start of the downtime and end of the downtime/uptime.
Thanks.
-
uptime.exe is a resource kit utility that will also show you the uptime for the server. If it's a dedicated SQL box, this should be close to SQL's uptime, though SQL could potentially stop and start without the server rebooting.
Steve Jones
-
Well if you want to generate a report then write a procedure to select the data from a dummy table in your userdb. If the select fails either the database is suspect or the server is down. Log the GETDATE() and other info you need in to a reporting table. Same thing when the select is success. You may need to define a Flag to avoid the duplicate data.
Shas3
-
That's a great point. If the server is running, but your database isn't, then is it up? Probably not.
You might be better off having some other process on another box running a "select" on the database, even a "select 1" to verify it's up.
Steve Jones
-
You can use a utility called pslist from the pstools suite at Sysinternals - http://www.sysinternals.com.
Create a quick script to run the pslist utility passing it the process name "sqlserver", it will tell you how long the process has been running ... thus telling you how long the MSSQLServer service has been active.
-
Because SQL server recreates Tempdb each time the server is stopped, you could use this script for tracking uptime.
SELECT DATEDIFF(hh,crdate,GETDATE()) AS UpTimeHrs
FROM MASTER.dbo.SYSDATABASES
WHERE name = 'TempDB'
-
checking SPID 1 login time versus current time will give you SQL server uptime. I use the following T-SQL to calculate number of Days my SQL servers are up:
SELECT CAST((CAST(datediff(ss, login_time, getdate()) as Decimal(9,2)) / 86400) as Numeric(6,2)) FROM master..sysprocesses WHERE spid = 1
-
How about a startup proc to create an entry in a table with a start_column = getdate() and with a sheduled job running every 5 min updating the last row, setting its enddate column to getdate(). Can also check if some databases are accessible. Next the rules when database not accessible
-
Hi Daniel,
quote:
Do anyone know if there's a easy way to check the uptime of the sql server and downtime and the logs for the downtime?this is not exactly what you asked for, but there several system variables I use for statistic purposes.
What about using a combination of @@cpu_busy, @@idle, @@io_busy.....?
I use this in a sp which runs once a day to collect these values
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply