Post reply

DB Offline

  • November 13, 2013 at 12:58 am

    #395809

    Hi

    In event log i can see 'Database option changed from Offline to On'. Whant to chekc why did the db get offline and when? Where can this informaiton be found?

    Thanks and regards

  • November 13, 2013 at 1:12 am

    #1665940

    Someone ran ALTER DATABASE ... SET OFFLINE, or that command is in a job somewhere. SQL won't take a DB offline by itself, someone or some app ran the ALTER DATABASE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2013 at 1:48 am

    #1665945

    Where can we see who did it? when it was done?

  • November 13, 2013 at 1:54 am

    #1665947

    Should be in the default trace. Otherwise ask the other DBAs, it required DB_owner permissions or high server level permissions, so not something that everyone should be able to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2013 at 2:09 am

    #1665956

    I have all the permissions. Can you explain where i can see this information.

    Also in the error log i can see the spid who has thurned the db on. But How can i find to which user this SPid had belonged to? (its this isue had occured 2 days back)

  • November 13, 2013 at 2:27 am

    #1665960

    Krishna1 (11/13/2013)

    Can you explain where i can see this information.

    I said in my previous post. It should be in the default trace.

    Also, taking a DB offline or bringing it online requires high permissions, so ask the people who have such permissions whether they ran the command and if so, why

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2013 at 2:41 am

    #1665962

    Hi

    Ok will check with them. I could open the trace file also. I would like to check with you if SQL server maiantains the history of the SPID and user

    Regards

  • November 13, 2013 at 2:47 am

    #1665964

    Krishna1 (11/13/2013)

    I would like to check with you if SQL server maiantains the history of the SPID and user

    It does not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2013 at 4:50 am

    #1666006

    In trace file can not see entry saying DB made offline

  • November 13, 2013 at 6:01 am

    #1666020

    The default trace doesn't keep data forever. It keeps 5 files of max 20MB each. Could be the files have rolled over and the date of the alter database is no longer in the trace.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2013 at 9:44 am

    #1666443

    Could this have happened following a reboot of the Windows box hosting SQL Server?

  • November 14, 2013 at 9:54 am

    #1666447

    crmitchell (11/14/2013)

    Could this have happened following a reboot of the Windows box hosting SQL Server?

    Only if, after the reboot, someone ran ALTER DATABASE <db name> SET OFFLINE.

    SQL will not set a DB offline. It can set a database recovering, recovery_pending or suspect, but offline is a status that is user-set.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2013 at 1:22 pm

    #1666528

    Krishna-Are you sure, you looked at SQL Server trace files thoroughly? As Gail already mentioned SQL Server will maintain only few trc files with 20 MB limit by default. For Any DDL activity against your Database you want to review from SQL Server default trace, I recommend you to simply right click on the database->go to Reports -> go to Standard reports - >go to Schema Changes History. This report pulls all(only) the DDL events from your default tracs file.

  • November 18, 2013 at 12:41 am

    #1667031

    Thanks Gail

  • November 19, 2013 at 8:46 pm

    #1667656

    Hi sreekanth

    Thanks. I saw the report it say DDL operation as ALTER but does not specify what alter command executed. Where can I find the details.

    Regards

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply