July 23, 2008 at 5:50 am
Comments posted to this topic are about the item How To Check SQL SERVER Uptime Through T-SQL
September 16, 2008 at 6:58 am
I added separation of the hours into days and hours.
SET NOCOUNT ON
DECLARE
@crdate DATETIME
, @days VARCHAR(3)
, @hr VARCHAR(50)
, @min-2 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-2=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min-2=(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
September 16, 2008 at 8:38 am
Nice script. Note that to get it to perform correctly on SQL Server 2005 you must change "sysdatabases" to "sys.sysdatabases".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 16, 2008 at 8:58 am
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
September 16, 2008 at 9:55 am
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-2 VARCHAR(5),
@today DATETIME
SET @today = GETDATE()
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,@today)
SET @days= @min-2/1440
SET @hr = (@min/60) - (@days * 24)
SET @min-2= @min-2 - ( (@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
September 16, 2008 at 9:58 am
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-2 VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
SET @days= @min-2/1440
SET @hr = (@min/60) - (@days * 24)
SET @min-2= @min-2 - ( (@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
September 16, 2008 at 10:52 am
got some cast errors with David's script.
try--
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days INT ,
@hr INT ,
@min-2 INT
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
SET @days= @min-2/1440
SET @hr = (@min/60) - (@days * 24)
SET @min-2= @min-2 - ( (@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
[font="Arial Narrow"]bc[/font]
September 16, 2008 at 12:00 pm
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
September 16, 2008 at 12:25 pm
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days INT ,
@hr INT ,
@min-2 INT
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min-2
gives me: 144069
---------------------------------
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min-2 VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min-2
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
[font="Arial Narrow"]bc[/font]
September 16, 2008 at 12:36 pm
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
September 16, 2008 at 12:46 pm
this script:
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min-2 VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min-2
PRINT '------------------------'
SET @days= @min-2/1440
gives this result:
*
------------------------
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value '*' to data type int.
So for whatever reason, @min-2 (varchar) is getting set with an asterisk value and then the script breaks when trying to set the @days variable because is can't do math on *.
[font="Arial Narrow"]bc[/font]
September 16, 2008 at 12:56 pm
i didn't get any error.
SET NOCOUNT ON
DECLARE @crdate DATETIME,
@days varchar(3),
@hr VARCHAR(50),
@min-2 VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE name='tempdb'
SET @min-2 = DATEDIFF (mi,@crdate,GETDATE())
PRINT @min-2
PRINT '-------------'
SET @days= @min-2/1440
print @days
PRINT '-------------'
Result:
24293
------------------------
16
------------------------
SQL DBA.
September 16, 2008 at 1:04 pm
I don't know why DATEDIFF would return a "*" rather than the number of minutes. At least the INT version works but still very odd.
I can run the query with VARCHAR or INT and both work fine.
David
September 16, 2008 at 1:29 pm
it's because the varchar(5) is not big enough to hold mine which is 6 characters. try setting yours to varchar(3) and you should get the same error.
bc
[font="Arial Narrow"]bc[/font]
September 16, 2008 at 1:38 pm
Good to know, thanks
David
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply