MS SQL Service Uptime record

Question

Post reply

MS SQL Service Uptime record

paksyeet

SSCommitted

Points: 1596
More actions
July 28, 2008 at 9:21 pm

#70480

Hi
is there a way(script) to find out how long did my ms sql server running?
like
DB Server Up since: dd/mm/yy Uptime: 120 days 3 hrs 30 min
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vikas S. Rajput SSC-Addicted Points: 475 More actions · Answer 1

Probably below is what you are looking for.

Script to capture uptime of SQL Server:

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'

What its based upon? Whenever you reinstantiate your SQL server, tempdb gets reinitiated. Hence am using that as the server start date and then taking the diff for it with current date. Please let me know if it works well (or not) for you.

Regards,

Vikas Rajput

_____________
Vikas S. Rajput

paksyeet SSCommitted Points: 1596 More actions · Answer 2

GREAT!!!..;p

thanks..

but on my side it give -1 days..

i've try to runn it on my local machine and the result is:

SQL Server “LOCAL” Uptime: -1 days, 25 hours & 1 minutes

i think i'll just have to find out myself... thanks.. this script help a lot..;p

Jpotucek SSCoach Points: 18583 More actions · Answer 3

I agree ! works great and very helpfull !!! Two questions:

would like to schedule this to run and then write to a file or email results... is there a way to do that?

Also, Is there a way to get 'SQL' uptime? like individual database uptime?

John Ericson Newbie Points: 9 More actions · Answer 4

Vikas Rajput (7/29/2008)SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

I had to modify this part to look like this for it to work in MS SQL 2008:

SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 5

Jpotucek (9/11/2008)
Also, Is there a way to get 'SQL' uptime? like individual database uptime?

That is 'SQL' uptime. tempdb is created when, and only when, SQL Server is restarted.

John