April 7, 2009 at 10:18 pm
Dear All,
Need to your help in calculating uptime for sql database servers.What are parameters we need to consider and how to implement it on monthly basis to generate a up time report.
I have one script which will exactly show uptime of the database service from the last restart of the sql services. But this script needs to be executed before every time the services stops. Is there any possibility of automatically executing this script before every time sql server service stop as there is option to execute the procedure every time the service start(sp_procoption).
regards
Kokila K
April 8, 2009 at 2:23 am
For my knowledge, there is no such event. When the service is (going) down or hangs it is obvious no scripts can be executed.
I think you should act on entries in the Windows Eventlog...
April 8, 2009 at 1:21 pm
It's possible I guess, except I actually don't know how to set a schedule to run when SQL stops
I have seen schedule or articles to run scripts when SQL starts
You could read the error logs, search for "shutdown", and calculate the duration between (which you can do AFTER a SQL start, not necessarily BEFORE SQL stops)
rowidentrytimesourcelogentry
42822009-03-17 16:01:07.760ServerSQL Server is terminating because of a system shutdown.
...usually followed by
27552009-03-17 16:05:55.790ServerDetected 8 CPUs. This is an informational message; no user action is required.
27642009-03-17 16:05:58.810spid5sSQL Trace ID 1 was started by login "sa".
April 8, 2009 at 1:30 pm
i wonder if you could create a proc that kicks off at startup that increments a
counter in a table somewhere.
if the servers down......no incrementing happens.
if its up....well...you get the picture.
April 8, 2009 at 1:51 pm
google found this, it gives you uptime of sql instance in minutes
SELECT datediff(mi, login_time, getdate())
FROM master..sysprocesses WHERE spid = 1
alternatively for server uptime try this[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 8, 2009 at 5:44 pm
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5)
SELECT @crdate=crdate FROM master.dbo.sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server “' + CONVERT(VARCHAR(30),SERVERPROPERTY('SERVERNAME'))+'” is Online for the past '+@hr+' hours & '+
@min-2+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply