Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adeoye Omoboya » How To Check SQL SERVER Uptime Through T-SQL
Add to briefcase
19 posts, Page 1 of 2
12»»

How To Check SQL SERVER Uptime Through T-SQL Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
adeomoboya
Posted Wednesday, July 23, 2008 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 09, 2012 10:55 AM
Points: 3, Visits: 35
Comments posted to this topic are about the item How To Check SQL SERVER Uptime Through T-SQL
Post #539148
Dwayne Adams
Posted Tuesday, September 16, 2008 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:06 PM
Points: 2, Visits: 130
I added separation of the hours into days and hours.


SET NOCOUNT ON
DECLARE
@crdate DATETIME
, @days VARCHAR(3)
, @hr VARCHAR(50)
, @min VARCHAR(5)

SELECT @crdate=crdate FROM master.dbo.sysdatabases WHERE NAME='tempdb'

SELECT @days=((DATEDIFF ( mi, @crdate,GETDATE()))/60)/24

SELECT @hr=((DATEDIFF ( mi, @crdate,GETDATE()))/60)- (@days * 24)

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
+@days+' days & '
+@hr+' hours & '
+@min+' 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
Post #570181
RBarryYoung
Posted Tuesday, September 16, 2008 8:38 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Nice script. Note that to get it to perform correctly on SQL Server 2005 you must change "sysdatabases" to "sys.sysdatabases".

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #570296
John H Marsh
Posted Tuesday, September 16, 2008 8:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 28, 2013 1:54 AM
Points: 1,325, Visits: 1,376
Hello,

Just one minor amendment so that the script runs Okay on a case sensitive Server:

SELECT @crdate=crdate FROM sysdatabases WHERE name='tempdb'

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group
Post #570325
DavidSimpson
Posted Tuesday, September 16, 2008 9:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:29 AM
Points: 890, Visits: 931
Only a minor thought but if you replace the formulas with variables, I think it makes the code easier to read.

SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min VARCHAR(5),
@today DATETIME

SET @today = GETDATE()

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,@today)
SET @days= @min/1440
SET @hr = (@min/60) - (@days * 24)
SET @min= @min - ( (@hr + (@days*24)) * 60)

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
+@days + ' days & '
+@hr+' hours & '
+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.sys.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


David


Post #570398
DavidSimpson
Posted Tuesday, September 16, 2008 9:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:29 AM
Points: 890, Visits: 931
After reworking it the @today is only use once so don't even need that variable.

SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
SET @days= @min/1440
SET @hr = (@min/60) - (@days * 24)
SET @min= @min - ( (@hr + (@days*24)) * 60)

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
+@days + ' days & '
+@hr+' hours & '
+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.sys.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


Post #570402
bc_
Posted Tuesday, September 16, 2008 10:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:42 AM
Points: 1,304, Visits: 7,118
got some cast errors with David's script.

try--

SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days INT ,
@hr INT ,
@min INT

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
SET @days= @min/1440
SET @hr = (@min/60) - (@days * 24)
SET @min= @min - ( (@hr + (@days*24)) * 60)

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '
+ CAST(@days AS VARCHAR(30))+ ' days & '
+ CAST(@hr AS VARCHAR(30))+' hours & '
+ CAST(@min AS VARCHAR(30))+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.sys.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent NOT running'
END
ELSE
BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

thanks,
bc

bc
Post #570448
DavidSimpson
Posted Tuesday, September 16, 2008 12:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:29 AM
Points: 890, Visits: 931
got some cast errors with David's script.


I didn't get any casting errors when I ran it but I agree your version is better with having the variables as INT and casting for printing purposes.

David


Post #570508
bc_
Posted Tuesday, September 16, 2008 12:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:42 AM
Points: 1,304, Visits: 7,118
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days INT ,
@hr INT ,
@min INT

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min

gives me: 144069
---------------------------------
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SET @min = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min

gives me: *

and then i got this when running the entire script with the varchars--
Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value '*' to data type int.

not sure whats going on there??

bc

bc
Post #570526
DavidSimpson
Posted Tuesday, September 16, 2008 12:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:29 AM
Points: 890, Visits: 931
and then i got this when running the entire script with the varchars--
Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value '*' to data type int.


I'm a bit confused because the script does not contain a "*". Can you post the entire script that gives you the error.

Thanks
David


Post #570530
« Prev Topic | Next Topic »

Add to briefcase
19 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.