locked
SQL Server Agent's Logging: Database Start and Stop: RRS feed

  • Question

  • Hi folks,
       recently I needed to see when a Database was took offline and then brought online again. Investigating trough the SQL Server Agent logs I can see that the latter event is traced, but the former (when the DB has gone offline) is not.

    How can I get this info?

    Regards
    Tuesday, April 7, 2009 9:16 AM

Answers

  • Hi,

    When the SQL Server stops and starts, a checkpoint will occur in the database. A checkpoint leads to a new active log. Therefore, if you want to view the former logs, you could choose the active logs you want to review. This can be down with the following instructions:
    1. Connect to the SQL Server instance by using SQL Server Management Studio.
    2. In Object Explorer, expand Management, right click SQL Server logs and select ViewSQL Server Log.
    3. In the Log File Viewer dialog box, select SQL Agent and choose the active logs.

    For more information about Checkpoints and the Active Portion of the Log, please see http://msdn.microsoft.com/en-us/library/ms189573(SQL.90).aspx.


    If there are any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, April 10, 2009 7:48 AM

All replies

  • Hello,

    Try this...

    SELECT

     

     state_desc,* FROM sys.databases
    WHERE name='testDB'

    Hope this is helpful...


    Thanks,
    Shanthi

    Tuesday, April 7, 2009 10:07 AM
  • Hi,

    When the SQL Server stops and starts, a checkpoint will occur in the database. A checkpoint leads to a new active log. Therefore, if you want to view the former logs, you could choose the active logs you want to review. This can be down with the following instructions:
    1. Connect to the SQL Server instance by using SQL Server Management Studio.
    2. In Object Explorer, expand Management, right click SQL Server logs and select ViewSQL Server Log.
    3. In the Log File Viewer dialog box, select SQL Agent and choose the active logs.

    For more information about Checkpoints and the Active Portion of the Log, please see http://msdn.microsoft.com/en-us/library/ms189573(SQL.90).aspx.


    If there are any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, April 10, 2009 7:48 AM