Find out how long the sql server service has been running, from t-sql

Find out how long the sql server service has been running, from t-sql

I'd like if its possible to work out from inside sql server how long sql server has been running.

Would like to use this in conjunction with one of the DMV's for unused indexes, but the counters are re-set every time sql server loads, so I'd like to know how useful they're going to be.

Answer by Lieven Keersmaekers for Find out how long the sql server service has been running, from t-sql

Grabbed here

USE Master  GO    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 <> running'  END  ELSE BEGIN  PRINT 'SQL Server and SQL Server Agent both are running'  END  

Answer by Lazy Bob for Find out how long the sql server service has been running, from t-sql

found this article that gets the login time from SPID 1, which is the system connection SPID:

http://www.sqlmag.com/Article/ArticleID/38042/sql_server_38042.html

Answer by AakashM for Find out how long the sql server service has been running, from t-sql

SELECT      login_time  FROM      sys.dm_exec_sessions  WHERE      session_id = 1  

will give you a

datetime for when the server was started.

Answer by kevchadders for Find out how long the sql server service has been running, from t-sql

To get it programmatically, you can run this script. It checks the creation time of your tempdb, since tempdb gets reinitialized every time Sql Server is started.

SELECT create_date   FROM sys.databases   WHERE name = 'tempdb'  

To make it more intuitive, you can run the script below, which will tell you how many days and hours Sql Server has been running. Minutes and seconds information will be truncated. If you need that, modify the script to get it yourself.

SELECT 'Sql Server Service has been running for about '  + CAST((DATEDIFF(hh, create_date, GETDATE()))/24 AS varchar(3)) + ' days and '  + CAST((DATEDIFF(hh, create_date, GETDATE())) % 24 AS varchar(2)) + ' hours'  FROM sys.databases   WHERE name = 'tempdb'  

Source: How long SQL Server has been running

Answer by adrianbanks for Find out how long the sql server service has been running, from t-sql

SELECT crdate FROM sysdatabases WHERE [name] = 'tempdb'

The above will work on SQL Server 2000, 2005 and 2008.

The logic is that the result from the above SQL returns the created date of the tempdb database, which SQL Server recreates every time it is restarted. Hence, the created date of tempdb is the startup time of the server.

Answer by Michel de Ruiter for Find out how long the sql server service has been running, from t-sql

This simple query works for versions before SQL Server 2005 as well as recent ones:

SELECT   crdate AS startup,   DATEDIFF(s, crdate, GETDATE()) / 3600. / 24 AS uptime_days  FROM master..sysdatabases  WHERE name = 'tempdb'  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72