none
Find out time my instance went down RRS feed

  • Question

  • Is there anyway that I can find out the time an instance went offline? Does the last entry in the error logs show this? One on my instances went down during the night and I need to know the time this happened if possible.

    Thanks in advance.

    Ryan

    Monday, May 18, 2015 11:03 AM

Answers

  • Hello Ryan,

    Yes, in the ErrorLog files you will find at the end an entry about the service shutdown together with a timestamp.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 18, 2015 11:04 AM
    Moderator
  • You have many options

    1.

    sp_readerrorlog 1

    2. See time when tempdb is created by right click on tempdb and selecting properties this will work if trace flag 3609 is not set

    3. Use DMV sys.dm_os_sys_info if you have SQl Server 2008 and above

    select sqlserver_start_time from 
    sys.dm_os_sys_info


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, May 18, 2015 11:12 AM
    Moderator

All replies

  • Hello Ryan,

    Yes, in the ErrorLog files you will find at the end an entry about the service shutdown together with a timestamp.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 18, 2015 11:04 AM
    Moderator
  • You have many options

    1.

    sp_readerrorlog 1

    2. See time when tempdb is created by right click on tempdb and selecting properties this will work if trace flag 3609 is not set

    3. Use DMV sys.dm_os_sys_info if you have SQl Server 2008 and above

    select sqlserver_start_time from 
    sys.dm_os_sys_info


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, May 18, 2015 11:12 AM
    Moderator
  • Hello Ryan - As Olaf mentioned, SQL Server error log will generally have the entry for shutdown event however in case of abrupt shutdown of machine or service crash you may have to check Windows Event Logs to get this info.

    In SQL Server error log file you should see entry similar to:

    "SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required."


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue


    Monday, May 18, 2015 11:14 AM
  • SET NOCOUNT ON
    DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
    SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'
    SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
    IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
    ELSE
    SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
    PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
    IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
    BEGIN
    PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
    END
    ELSE BEGIN
    PRINT 'SQL Server and SQL Server Agent both are running'
    END

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 18, 2015 11:14 AM
    Moderator
  • Uri, that script doesn't show when the SQL Servwr was shutodown. It show for how long it has been running. If you start it immediately when it is shutdown, then these two might be considered equal (more or less). But if you don't start your istanatnce immediately, or need a more exact time, then the difference is important.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, May 18, 2015 11:48 AM
    Moderator
  • I generally look at

    1. ERRORLOGs
    2. System Event Logs
    3. Application Event Logs
    4. Cluster Logs (if applicable)

    at the time of restart.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Monday, May 18, 2015 11:55 AM
    Moderator
  • Yep, I simple calculate 

    SELECT DAY(GETDATE())-366 /24 ... on May 3 the server was offline but it can be offline for a week..

    I thing the only way to get it is to traverse via ERROL.LOG to look for the message...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 18, 2015 11:58 AM
    Moderator
  • Hello Ryan - Have a look at this code snippet, this enumerates & reads SQL Server Error log file and figures out the Date & Time when Termination event was triggered.

    You can try this to get some more insights:

    -- Creating some temp tables to hold intermediate results
    CREATE TABLE #tmpShutdownEvents
    (
    	LogDate DATETIME,
    	ProcessInfo VARCHAR(100),
    	[Text] VARCHAR(500)
    )
    
    CREATE TABLE #tmpEnumerator
    (
    	ArchiveNo INT,
    	FileDate DATETIME,
    	LogFileSize INT
    )
    
    -- Build log enumerator
    INSERT INTO #tmpEnumerator
    exec sp_enumerrorlogs
    
    -- Build Cursor to traverse the log files, read them for event
    DECLARE @ArchiveNo SMALLINT
    DECLARE curEnumerator CURSOR 
    	FOR SELECT ArchiveNo FROM #tmpEnumerator
    
    	OPEN curEnumerator
    	FETCH NEXT FROM curEnumerator INTO @ArchiveNo
    	
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		INSERT INTO #tmpShutdownEvents
    		exec xp_readerrorlog @ArchiveNo,1,'SQL Server is terminating'
    		FETCH NEXT FROM curEnumerator INTO @ArchiveNo
    	END
    	
    CLOSE curEnumerator	
    DEALLOCATE curEnumerator	
    
    
    -- Display what's in the store
    SELECT * FROM #tmpShutdownEvents
    
    -- Clean-up
    DROP TABLE #tmpShutdownEvents
    DROP TABLE #tmpEnumerator


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue


    Monday, May 18, 2015 12:51 PM