Startup Times (Windows Server, SQL Server and DMV Stats)

It is often necessary to know when the SQL Server was last restarted and if the DMV's are accumulating since then or, on the other hand, if someone cleaned up the statistics with the command:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

In this context, using part of the script from this post, it is possible to get this information directly or indirectly, depending on the version of SQL Server that we are using.

So we get:

SET NOCOUNT ON;
GO

DECLARE @SQL_Version              AS numeric (4, 1);

DECLARE @WINSrv_Startup_Time      AS datetime;
DECLARE @SQLSrv_Startup_Time      AS datetime;
DECLARE @WaitStats_Cleanup_Time   AS datetime;


/* Get SQL Server Version @SQL_Version */
SET @SQL_Version = LEFT(CAST(SERVERPROPERTY('productversion') AS varchar ), 4);

/* Get Windows Server Startup Time @WINSrv_Startup_Time */
SET @WINSrv_Startup_Time = (SELECT DATEADD(S, - ms_ticks/ 1000, GETDATE()) FROM sys.dm_os_sys_info);
  /* Changed to seconds because there is integer overflow if the server has been restarted for more than 2147483647ms (more than 25 days).*/

/* Get SQL Server Startup Time @SQLSrv_Startup_Time */
IF @SQL_Version < 10 -- < 2008
  SET @SQLSrv_Startup_Time = (SELECT crdate FROM sys.sysdatabases WHERE dbid = 2) /*tempdb creation date*/
ELSE
  SET @SQLSrv_Startup_Time = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info)

/* Get SQL Server Wait Statistics Cleanup Time @WaitStats_Cleanup_Time */
IF @SQL_Version < 10 -- < 2008
  SET @WaitStats_Cleanup_Time = (SELECT DATEADD (S, - wait_time_ms / 1000, GETDATE()) FROM sys.dm_os_wait_stats WHERE wait_type = 'SQLTRACE_BUFFER_FLUSH')
ELSE
  SET @WaitStats_Cleanup_Time = (SELECT DATEADD (S, - wait_time_ms / 1000, GETDATE()) FROM sys.dm_os_wait_stats WHERE wait_type = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')

/* Show values */
SELECT
    @WINSrv_Startup_Time    AS 'Windows Server Startup Time'
  , @SQLSrv_Startup_Time    AS 'SQL Server Startup Time'
  , @WaitStats_Cleanup_Time AS 'SQL Server Wait Statistics Cleanup Time';


SET NOCOUNT OFF;
GO

Would point out that sometimes your SQL Server 2005 does not have all the information in the DMVs. So we have to obtain the information indirectly: we can get the starting date of the SQL Server service through the creation date of tempdb.

For the time accumulation information of DMVs, the query was developed based on this post from SQLOS & Cloud Infrastructure Team Blog