reporting on SQL uptime

[jpotucek]

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 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=(DATEDIFF ( hh, @crdate,GETDATE()))
ELSE
SELECT @min=(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'

 

[JayKusch]

Unless your DBs are set for Auto_Close, what reason would you need to know the uptime for individual DBs?

Or as you as to know when each DB was created?

If that is the case, you could modify the code with a MSForEachDB and run your date comparison on the crdate or create_date based on the version of SQL Server you are running.

As for capturing this info, you could easily assign a varaible to build the info in and then pop it in a table or wrap it in a DBmail store procedure if you are using SS2K.

Thanks

J. Kusch

 

[MDXer]

I believe the code is intended to show how long the SQL service has been running since tempdb is recreated when SQL starts.


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck