Speak guys!
All in peace??
With this article, I would like to show you how to find out how long the instance has been online or when the instance was started. This information can be used in a variety of ways, such as creating a monitoring of when the SQL service was restarted and then starting an audit of who restarted or why it restarted the service, for example.
I will demonstrate some solutions that I know below and note that, usually dates have slight differences with the date / time returned between the methods.
Alternative #1: dm_os_sys_info
A practical and simple way to know when the SQL Server service was last started is by looking at the sys.dm_os_sys_info DMV, available from SQL Server 2008. This is the most reliable way to obtain this information..
Required Permission: VIEW SERVER STATE
|
1 |
SELECT sqlserver_start_time FROM sys.dm_os_sys_info |
Result:
#2 Alternative: sys.databases
Another way to get when the service was last started is to look at the sys.databases system view (available from SQL Server 2008) and look at the creation date of the tempdb database. As you know, this database is recreated every time the instance is started.
Required Permission: VIEW SERVER STATE
|
1 2 3 |
SELECT create_date FROM sys.databases WHERE [name] = 'tempdb' |
Result:
However, tempdb rebuild is not the first thing SQL Server does during the SQL service upgrades process, including user databases being started (recovery process) before tempdb rebuild. In scenarios where the database recovery routine takes too long, there may be a big difference between the instance startup time and the tempdb rebuild date.
Alternative #3: sys.sysdatabases
Another view we can use to meet the need for this post is sys.sysdatabases, available since SQL Server 2000. The idea is the same as sys.databases (Alternative #2).
Required Permission: VIEW SERVER STATE
|
1 2 3 |
SELECT crdate FROM sys.sysdatabases WHERE [name] = 'tempdb' |
Result:
It is worth mentioning that this view is marked as deprecated and will be removed in future versions of SQL Server, so I recommend using this alternative only in cases of backward compatibility.
#4 Alternative: sys.dm_exec_sessions
Using the sys.dm_exec_sessions view (available from SQL Server 2008) we can also meet the need for this post when filtering through session 1 (sessions with sid <50 are all internal to SQL).
Required Permission: VIEW SERVER STATE
|
1 2 3 |
SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1 |
Result:
#5 Alternative: sys.traces
Using the sys.traces view (available from SQL Server 2005) filtering by the is_default = 1 column, you will see the default SQL Server trace creation date, which is recreated each time the instance is restarted.
Required permission: VIEW SERVER STATE and ALTER TRACE
|
1 2 3 |
SELECT start_time FROM sys.traces WHERE is_default = 1 |
Result:
Note that this feature has been marked deprecated from SQL Server 2012, and should be replaced in future versions of the product by system_health, which implements this feature using Extended Events (XE).
Alternative #6: sys.dm_io_virtual_file_stats
Introducing one more alternative, we have the DMV sys.dm_io_virtual_file_stats, which brings us I / O statistics for database data and log files.
Required Permission: VIEW SERVER STATE
|
1 2 |
SELECT DATEADD(ms, -sample_ms, GETDATE()) AS StartTime FROM sys.dm_io_virtual_file_stats(1,1) |
Result:
To meet what we need, let's use the sample_ms column, which, according to BOL, returns the number of milliseconds since the computer was started. That is, with this query, we have information even before the start of SQL Server, which is when the server running the SQL Server service was started.
As you may have noticed from the print above, there was probably a bug in this DMV, as the theoretical server start date is well past the other SQL Server service start dates, using the other alternatives in this post.
Alternative #7: xp_readerrorlog or sp_readerrorlog
Using the xp_readerrorlog system stored procedure (available from SQL Server 2005), we can read the SQL Server log file and look for the string that contains the log record when the service was started.
This information, though a bit harder to obtain, is one of the most reliable, though Microsoft documentation tells us that the sp_cycle_errorlog system SP can be used internally to recycle log files without restarting the instance. , causing you to be unable to retrieve this information if it happens.
Required permission: VIEW SERVER STATE and being a member of server role securityadmin
|
1 2 3 4 5 6 |
DECLARE @Retorno TABLE ( [LogDate] DATETIME, [ProcessInfo] NVARCHAR(12), [Text] NVARCHAR(3999) ) INSERT INTO @Retorno EXEC xp_readerrorlog 0, 1, N'Copyright (c) Microsoft Corporation' SELECT LogDate FROM @Retorno |
Results:
The xp_readerrorlog system SP and sp_readerrorlog are very similar to each other. In fact, sp_readerrorlog performs some data entry handling and internally executes xp_readerrorlog.
Note that there are some bug reports in 2012 upwards when trying to use a varchar value in xp_readerrorlog's 3º and 4º parameters (works with NVARCHAR only), and that does not occur in sp_readerrorlog.
Error Examples:
If you want to know a little more about these system SP's, read my post The undocumented SQL Server extended procedures.
#8 Alternative: sys.dm_server_services
Using the sys.dm_server_services DMV (available from SQL Server 2008 R2 SP1), we can verify information regarding instance services, such as the SQL Server service itself and also SQL Agent, Full-text search, and others.
Required Permission: VIEW SERVER STATE
|
1 2 3 |
SELECT last_startup_time FROM sys.dm_server_services WHERE ServiceName LIKE 'SQL Server (%' |
Result:
Alternative #9: sys.sysprocesses
Using the sys.sysprocesses system view (available from SQL Server 2000), we can verify information regarding the open processes in the instance. To try to identify the date the instance went online, let's take the first open process (spid = 1), a technique similar to the #4 alternative.
Required Permission: VIEW SERVER STATE
|
1 2 3 |
SELECT login_time FROM sys.sysprocesses WHERE spid = 1 |
Result:
#10 Alternative: msdb.dbo.syssessions
Each time the SQL Agent service starts, a record in the msdb.dbo.syssessions table is added with the new Agent session ID and date / time of this event. This table records the history of when Agent was started.
This service is typically started together with the SQL Server service, but this is a point of failure for this alternative (when it does not). Another weakness of this solution is that Agent takes a few seconds to start, so the time is not so synchronized with the SQL Server service start time.
If you want to see some more examples of syssessions, read my post How to query SQL Agent startup history in SQL Server.
Required Permission: VIEW SERVER STATE
|
1 2 |
SELECT MAX(agent_start_date) AS agent_start_date FROM msdb.dbo.syssessions |
Result:
That's it folks!
I hope you enjoyed this post and see you next time!
sql server how long the instance has been up online when the service started
sql server how long the instance has been up online when the service started
sql server when datetime time instance service became online up bring online
sql server when datetime time instance service became online up bring online
