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