Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How To Check SQL SERVER Uptime Through T-SQL Expand / Collapse
Author
Message
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 9, 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
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: Wednesday, May 21, 2014 5:23 AM
Points: 2, Visits: 163
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
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: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
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: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
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: Yesterday @ 10:02 AM
Points: 916, Visits: 992
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
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: Yesterday @ 10:02 AM
Points: 916, Visits: 992
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
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, July 21, 2014 8:27 AM
Points: 1,343, Visits: 7,176
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
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: Yesterday @ 10:02 AM
Points: 916, Visits: 992
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
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, July 21, 2014 8:27 AM
Points: 1,343, Visits: 7,176
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
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: Yesterday @ 10:02 AM
Points: 916, Visits: 992
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 12»»

Permissions Expand / Collapse