How To Check SQL SERVER Uptime Through T-SQL

Formular una pregunta

miércoles, 06 de agosto de 2008 10:14

1
logon to SQL Server management studio and connect to the master database instance

The after that you run this script against the master database instance only...

Highlighted Below:

SQLScript:

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases 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
- Tipo cambiado Phil BrammerMVP, Moderator lunes, 11 de enero de 2010 17:52
- Tipo cambiado Phil BrammerMVP, Moderator lunes, 11 de enero de 2010 17:52
- Tipo cambiado Tom PhillipsModerator martes, 12 de enero de 2010 2:16
- Cambiado Tom PhillipsModerator lunes, 16 de agosto de 2010 19:29 TSQL Question (From:SQL Server Database Engine)
- Responder
- Citar

Todas las respuestas

miércoles, 06 de agosto de 2008 16:24

Moderador

1
Nice script!

I tested it. I added USE master to be complete:

Code Snippet

USE

master;

SET

NOCOUNT ON

DECLARE

@crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT

@crdate=crdate FROM sysdatabases 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

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
- Propuesto como respuesta Naomi NMicrosoft Community Contributor, Moderator lunes, 13 de septiembre de 2010 20:02
- Marcado como respuesta Kalman TothMicrosoft Community Contributor, Moderator jueves, 29 de diciembre de 2011 22:54
- Editado Kalman TothMicrosoft Community Contributor, Moderator sábado, 29 de septiembre de 2012 22:50
- Responder
- Citar
martes, 07 de octubre de 2008 15:25

Moderador

0

Adeoye

I notice that you are posting seemingly random bits of information.

It would be far more useful to the Forums if you were to offer your assistance to Users by posting suggestions to their problems.

These 'random' thoughts aren't really helping anyone...
- Responder
- Citar
domingo, 02 de agosto de 2009 8:24

0

Great job. very nice code.
Too Soon Old, Too Late Smart
- Responder
- Citar
lunes, 11 de enero de 2010 17:29

0

Gave an Error Invalid Colmn Line 3 cloumn NAME. Change it to lower case to reolve.
- Responder
- Citar

lunes, 16 de agosto de 2010 5:52

Here is what it takes to run on SQL Server 2008 R2:

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

Propuesto como respuesta Kalman TothMicrosoft Community Contributor, Moderator lunes, 16 de agosto de 2010 7:40
Marcado como respuesta Kalman TothMicrosoft Community Contributor, Moderator viernes, 22 de octubre de 2010 14:51

lunes, 16 de agosto de 2010 6:56

Usuario que responde

0
This script runs as well on SQL Server 2005 :-)
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
- Marcado como respuesta Kalman TothMicrosoft Community Contributor, Moderator jueves, 29 de diciembre de 2011 22:54
- Responder
- Citar

lunes, 13 de septiembre de 2010 19:59

The following script attempts to get the uptime too, but why is there a difference? Which of these should be considered more accurate?

 WITH ServerUpTimeInfo AS (
   SELECT (dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00
      AS server_up_time_min,
     ((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00
      AS server_up_time_hr,
     (((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00) / 24.00
      AS server_up_time_day
   FROM sys.dm_io_virtual_file_stats(1,1) AS dm_io_virtual_file_stats )
 SELECT CAST(server_up_time_min AS decimal(12,2)) AS server_up_time_min,
   CAST(server_up_time_hr AS decimal(12,2)) AS server_up_time_hr,
   CAST(server_up_time_day AS decimal(12,2)) AS server_up_time_day,
   CAST(DATEADD(n,
     -ROUND(server_up_time_min, -1),
     DATEADD(hh, -ROUND(server_up_time_hr, -1), DATEADD(d, -ROUND(server_up_time_day, -1), GETUTCDATE()))
     ) AS smalldatetime)
      AS approx_server_start_utc_datetime
 FROM ServerUpTimeInfo;
 GO

viernes, 17 de diciembre de 2010 20:20

0

Aalam,

sys.dm_io_virtual_file_stats returns the start time of the computer, not the start time of the SQL Server instance. See http://www2.sqlity.net/SQLServerUptime for more details.
- Responder
- Citar
jueves, 29 de diciembre de 2011 20:08

0
Hi all,

If anyone has idea of how to get uptime of sql server databases? Whether there is any such table that we can query for individual database uptime or its not possible?

Thanks for your valuable inputs.

J_BABARIA
- Editado j_babaria jueves, 29 de diciembre de 2011 20:08
- Responder
- Citar
lunes, 30 de enero de 2012 15:12

1

Aalam, you redundantly added hours, minutes and days, which are the same information in different forms, so the final date was way off.

Try this for server uptime (4 minutes more than SQL uptime, on the server I'm looking at.)

WITH ServerUpTimeInfo AS (

SELECT (dm_io_virtual_file_stats.sample_ms / 1e3 )

AS server_up_time_sec,

(dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00

AS server_up_time_min,

((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00

AS server_up_time_hr,

(((dm_io_virtual_file_stats.sample_ms / 1000.00 ) / 60.00) / 60.00) / 24.00

AS server_up_time_day

FROM sys.dm_io_virtual_file_stats(1,1) AS dm_io_virtual_file_stats )

SELECT CAST(server_up_time_min AS decimal(12,2)) AS server_up_time_min,

CAST(server_up_time_hr AS decimal(12,2)) AS server_up_time_hr,

CAST(server_up_time_day AS decimal(12,2)) AS server_up_time_day,

CAST(DATEADD(second,-server_up_time_sec,GETUTCDATE()) AS smalldatetime)

AS approx_server_start_utc_datetime,

CAST(DATEADD(second,-server_up_time_sec,GETDATE()) AS smalldatetime)

AS approx_server_start_localtime

FROM ServerUpTimeInfo;

GO
- Responder
- Citar
jueves, 26 de abril de 2012 3:53

0
Hi All,

Here is the same script, also via UI u can get it right click the tempdb and created date :)

=============================

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

============================

Regards

RajU R
- Propuesto como respuesta Artem Ervits martes, 01 de mayo de 2012 20:29
- Votado como útil Artem Ervits martes, 01 de mayo de 2012 20:29
- Responder
- Citar
martes, 01 de mayo de 2012 20:30

0

I usually check the create date database property on tempdb.

select create_date from sys.databases where name = 'tempdb'
- Responder
- Citar
martes, 01 de mayo de 2012 20:33

2
in 2008r2 additional field was added to sys.dm_os_sys_info called sqlserver_start_time

select sqlserver_start_time from sys.dm_os_sys_info
- Editado Artem Ervits martes, 01 de mayo de 2012 20:33
- Propuesto como respuesta Microslave2k jueves, 13 de septiembre de 2012 9:34
- Responder
- Citar
miércoles, 04 de julio de 2012 16:46

0

Hi,

The script works fine. However, I want to direct the output into a table. Can it be done ?

Thanks

=====
- Responder
- Citar
miércoles, 04 de julio de 2012 16:49

Moderador

1

Yes, you can create a table ServerState (IdleMessage varchar(max))

and instead of the PRINT statements use

insert into ServerState (IdleMessage)

select -- here follows what is currently printed
For every expert, there is an equal and opposite expert. - Becker's Law

My blog
- Responder
- Citar
jueves, 05 de julio de 2012 16:06

0

Thanks.
- Responder
- Citar

How To Check SQL SERVER Uptime Through T-SQL

Todas las respuestas

Productos

clave

Soluciones

Actualizaciones

Pruebas

Sitios relacionados

Aprendizaje

Certificaciones

Otros recursos

Por producto

Otros vínculos