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 Tuesday, September 16, 2008 12:46 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
this script:

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
PRINT '------------------------'
SET @days= @min/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 (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 *.


bc
Post #570535
Posted Tuesday, September 16, 2008 12:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
i didn't get any error.


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
PRINT '-------------'
SET @days= @min/1440
print @days
PRINT '-------------'

Result:

24293
------------------------
16
------------------------


SQL DBA.
Post #570541
Posted Tuesday, September 16, 2008 1:04 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: Thursday, August 28, 2014 8:33 AM
Points: 923, Visits: 996
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



Post #570552
Posted Tuesday, September 16, 2008 1:29 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
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



bc
Post #570566
Posted Tuesday, September 16, 2008 1:38 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: Thursday, August 28, 2014 8:33 AM
Points: 923, Visits: 996
Good to know, thanks

David



Post #570573
Posted Wednesday, July 15, 2009 3:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:05 AM
Points: 67, Visits: 203
Please bear with me as I'm somewhat of a novice when it comes to scripting. This is a handy script, but here's a challenge for someone. Running the script as is obviously shows the uptime of SQL Server on the server, but I notice that when I replace tempdb with any of the user database names on the server, the uptime changes to reflect what I hope is the uptime for that database.

Here's the challenge: What modifications need to be done to this handy script to show the database name and the uptime of each database on the server?

If that was pretty easy, what further modifications would be needed to loop through a list of all of my servers to produce a report of the uptime of each server and of each user DB on each server?

Thanks!
Post #753875
Posted Wednesday, July 15, 2009 4:35 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: Thursday, August 28, 2014 8:33 AM
Points: 923, Visits: 996
The script actually returns the amount of time since the database was created. Since the tempdb is created every time SQL Server is started, this gives you the up-time for SQL Server.

You can run this to get list of all database names, total minutes, total days, hours within days and minutes within hours since each database has been created.
SELECT @@servername,
name, DATEDIFF (mi,crdate,GETDATE()) as 'Total Minutes',
DATEDIFF (day,crdate,GETDATE()) as 'Total Days',
(DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440)) / 60 as 'Hours within day',
DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440) -
(((DATEDIFF (mi,crdate,GETDATE()) - (DATEDIFF (day,crdate,GETDATE()) * 1440)) / 60) * 60) as 'Minutes with hour'
FROM sysdatabases
ORDER BY name

As for looping through all your servers here are couple of options:
1) Create linked servers and run the query against each of them.
2) Create SSIS package that loops through a list of servers and runs the script

For either of these, you can load the data into a local working table then run a final query to pull that data out of the working table. You probably would want to include @@servername in the query so your results will include the server name.

Hope this helps
David



Post #753882
Posted Wednesday, June 23, 2010 1:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:27 AM
Points: 45, Visits: 532
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
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 '+@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 <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
select * from sys.databases


Two corrections needs to be done in the script for successful execution

1. Change sysdatabases to sys.databases
2. Change the 3 line in the script (query) from "SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'" to "SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'". The error in the original query is, there is no such column (crdate) exists in the sys.databases table.

Correct me if am wrong anywhere.

Thanks,
Nagarjun.
Post #941538
Posted Thursday, April 4, 2013 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 9, 2013 5:35 AM
Points: 6, Visits: 56
Why not just using DMV for 2008+

SELECT sqlserver_start_time from sys.dm_os_sys_info
Post #1438681
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse