SQL Server Instance Start - Date Time Finding

SELECT
[sqlserver_start_time] AS [LastStartupDate]

FROM
[sys].[dm_os_sys_info]

go

SELECT
[crdate] AS [LastStartupDate]

FROM
[dbo].[sysdatabases]

WHERE
[name] = 'tempdb'

AND [crdate] < GETDATE() -- 90 --filter results to instances restarted greater than X days

GO


--search the current sql server error log for the the startup process id


DECLARE
@LogNum TINYINT = 0 --0=current 1=.1 etc

DECLARE
@LogType TINYINT = 1 --1=SQL 2=Agent

DECLARE
@ProcessType VARCHAR(64) = 'Server'

DECLARE
@TextSearch VARCHAR(20) = 'Server process ID is'

DECLARE
@ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT
INTO @ErrLog EXEC sys.xp_readerrorlog @LogNum, @LogType, @ProcessType, @TextSearch



--grab the first occurrence and report back the timestamp as the last startup


DECLARE
@LastRestart DATETIME

SELECT
@LastRestart = MIN(LogDate) FROM @ErrLog

SELECT
@LastRestart AS [Last Restart]

WHERE
@LastRestart < GETDATE() -- 90 --filter results to instances restarted greater than X days

GO