How To Check SQL SERVER Uptime Through T-SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

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

19 posts, Page 2 of 2

««1 2

How To Check SQL SERVER Uptime Through T-SQL

Rate Topic

Display Mode

Topic Options

Author

Message

bc_

Posted Tuesday, September 16, 2008 12:46 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,304, Visits: 7,119

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

SanjayAttray

Posted Tuesday, September 16, 2008 12:56 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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

DavidSimpson

Posted Tuesday, September 16, 2008 1:04 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 890, Visits: 932

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

bc_

Posted Tuesday, September 16, 2008 1:29 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,304, Visits: 7,119

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

DavidSimpson

Posted Tuesday, September 16, 2008 1:38 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 890, Visits: 932

Good to know, thanks

David

Post #570573

Steve Margolis

Posted Wednesday, July 15, 2009 3:45 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:49 AM
Points: 66, Visits: 165

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

DavidSimpson

Posted Wednesday, July 15, 2009 4:35 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 890, Visits: 932

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

ArjunaReddy

Posted Wednesday, June 23, 2010 1:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:08 PM
Points: 39, Visits: 388

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

edrost

Posted Thursday, April 04, 2013 2:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:09 AM
Points: 1, Visits: 36

Why not just using DMV for 2008+

SELECT sqlserver_start_time from sys.dm_os_sys_info

Post #1438681

« Prev Topic | Next Topic »

19 posts, Page 2 of 2

««1 2

Permissions