How long is running SQL Server

How long is running SQL Server

To know how much time my SQL Server is running, we can mention 3 ways:

1. Check on sys.sysprocesses the spid = 1, it is an internal process that is created when the SQL Server instance is started.

2. Check the creation date of the tempdb database. It can be checked in sys.databases on the column create_date.

3. Look into the ERRORLOG. The first line is logged when the SQL Server is started.

For test purpose, you can check the steps described below before and after restart the SQL Server service.

-- 0.

-- Current date-time

select getdate()

-----------------------

2009-06-03 04:15:27.513

(1 row(s) affected)

/*

1. Using sys.sysprocesses

Here we can see how much time the SQL Server using

the login_time column and sys.sysprocesses

*/

select

login_time as SQLStart,

datediff(mi, login_time, getdate()) Running_in_Minutes,

datediff(hh, login_time, getdate()) Running_in_Hours,

datediff(dd, login_time, getdate()) Running_in_Days

from sys.sysprocesses where spid = 1

Result

SQLStart Running_in_Minutes Running_in_Hours Running_in_Days

----------------------- ------------------ ---------------- ---------------

2009-06-03 04:13:11.793 2 0 0

(1 row(s) affected)

/*

2. Using sys.databases

Here we can see the column create_date for the

TEMPDB database.

*/

select

create_date as SQLStart,

datediff(mi, create_date, getdate()) Running_in_Minutes,

datediff(hh, create_date, getdate()) Running_in_Hours,

datediff(dd, create_date, getdate()) Running_in_Days

from sys.databases where name = 'tempdb'

Result.

SQLStart Running_in_Minutes Running_in_Hours Running_in_Days

----------------------- ------------------ ---------------- ---------------

2009-06-03 04:13:11.763 2 0 0

(1 row(s) affected)

/*

3. Using ERRORLOG

Executing the SP xp_readerrorlog, we can see the date and time exactly that SQL Server starts to logged activity.

*/

exec master..xp_readerrorlog

Result.

LogDate ProcessInfo Text

----------------------- ------------ ----------------------

2009-06-03 04:13:08.290 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation