September 11, 2008 at 12:57 pm
I cannot take credit for this code - I found it in another forum and it works for SQL2000 and 2005.
I have 2 questions about modifying it to fit my needs:
First, is there a way to schedule this job in a job and have it write results to a file or perhaps email the results? if so, how would I do that.
Also, How would you modify this to report on individual Database uptime?
Thank you!!
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5), @days VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @days=(DATEDIFF (dd, @crdate,GETDATE())-1)
SELECT @hr=(DATEDIFF ( hh, @crdate,GETDATE())-@days*24)
IF ((DATEDIFF ( hh, @crdate,GETDATE()))/60)=0
SELECT @min-2=(DATEDIFF ( hh, @crdate,GETDATE()))
ELSE
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server “' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'” Uptime: '+@days+' days, '+@hr+' hours & '+@min+' minutes'
September 11, 2008 at 2:12 pm
about Database uptime: as far as i know there is nothing to prove database uptime. Except when it was last used.(lookup db objects)
yes you can create the job to send you the message in HTML format.
something like this:
try this first on your test Box and test it throughly.
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5), @days VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @days=(DATEDIFF (dd, @crdate,GETDATE())-1)
SELECT @hr=(DATEDIFF ( hh, @crdate,GETDATE())-@days*24)
IF ((DATEDIFF ( hh, @crdate,GETDATE()))/60)=0
SELECT @min-2=(DATEDIFF ( hh, @crdate,GETDATE()))
ELSE
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
SET @tableHTML =
N' ' +
N' '+
CAST((SELECT td='SQL Server “' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'” Uptime: '+@days+' days, '+@hr+' hours & '+@min+' minutes'+''
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail @recipients=youremail.com',
@subject = 'SERVER UPTIME INFO',
@body = @tableHTML,
@body_format = 'HTML' ;
Maninder
www.dbanation.com
September 11, 2008 at 4:36 pm
The easiest way to find server up time is checking spid 1 in sysprocesses:
SELECT
[login_time]
,DATEDIFF(mm, [login_time], GETDATE()) AS [Days]
,DATEDIFF(dd, [login_time], GETDATE()) AS [Months]
,DATEDIFF(mi, [login_time], GETDATE()) AS [Minutes]
FROM [master].[dbo].[sysprocesses]
WHERE [spid] = 1
As for individual database up time, you can parse that out of the error log as well for as when the database was started up ... and again though, this is assuming that you don't reset your logs at a specified interval (off by default).
IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS NOT NULL
DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog
(
[LogDate]DATETIME
,[ProcessInfo]VARCHAR(32)
,[Text]VARCHAR(MAX)
)
INSERT INTO #ErrorLog
EXEC sp_readerrorlog
SELECT * FROM #ErrorLog
September 12, 2008 at 6:56 am
This looks like it will work for me... but I am getting Line 14: Incorrect syntax near 'XML'.
???
DECLARE @tableHTML NVARCHAR(50);
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5), @days VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @days=(DATEDIFF (dd, @crdate,GETDATE())-1)
SELECT @hr=(DATEDIFF ( hh, @crdate,GETDATE())-@days*24)
IF ((DATEDIFF ( hh, @crdate,GETDATE()))/60)=0
SELECT @min-2=(DATEDIFF ( hh, @crdate,GETDATE()))
ELSE
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
SET @tableHTML =
N' ' +
N' '+
CAST((SELECT td='SQL Server “' + CONVERT(VARCHAR(20),SERVERPROPERTY('KOCLAB02'))+'” Uptime: '+@days+' days, '+@hr+' hours & '+@min+' minutes'+''
FOR XML PATH('tr'), TYPE
AS NVARCHAR(50)) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='janice.richardson@kofc.org',
@subject = 'SERVER UPTIME INFO',
@body = @tableHTML,
@body_format = 'HTML'
September 12, 2008 at 7:48 am
well ... here's what I used...
http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html
-- how to determin SQLServer Uptime
-- Source: Tracking Uptime by Brian Moran http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html
--
--
SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, login_time as StartUp_DateTime
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM sys.dm_exec_sessions
WHERE session_id = 1
go
SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM sys.dm_exec_sessions
WHERE session_id = 1
) a
go
SELECT @@servername as ServerName
, SQLServer_UpTime_YY
, SQLServer_UpTime_MM
, SQLServer_UpTime_DD
, (SQLServer_UpTime_mi % 1440) / 60 as NoHours
, (SQLServer_UpTime_mi % 60) as NoMinutes
from (
SELECT datediff(yy, login_time, getdate()) as SQLServer_UpTime_YY
, datediff(mm, dateadd(yy,datediff(yy, login_time, getdate()),login_time) , getdate()) as SQLServer_UpTime_MM
, datediff(dd, dateadd(mm,datediff(mm, dateadd(yy,datediff(yy, login_time, getdate()),login_time) , getdate()) ,login_time) , getdate()) as SQLServer_UpTime_DD
, datediff(mi, login_time, getdate()) as SQLServer_UpTime_mi
FROM sys.dm_exec_sessions
WHERE session_id = 1
) a
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply