How To Check SQL SERVER Uptime Through T-SQL

Question

How To Check SQL SERVER Uptime Through T-SQL

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Steve Margolis SSC Enthusiast Points: 192 More actions · Answer 1

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!

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 2

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

ArjunaReddy Default port Points: 1467 More actions · Answer 3

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 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-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 '+@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.

edrost Valued Member Points: 68 More actions · Answer 4

Why not just using DMV for 2008+

SELECT sqlserver_start_time from sys.dm_os_sys_info