DB gone offline

  • Hello Masters,

    What are the reasons for any DB gone Offline ? How could I know at what time and who and why it went offline ? I checked Error logs and found nothing useful.

    Please help me

  • i'm Sure there must be something in error log or atleast in Event Viewer logs.

    Reason may be any long transaction got interrupted due to network problem .

    Also check ,Auto_close option is set to true or false

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • Thanks sanket for prompt reply..

    Can you guide me where to check the auto close option. Bcos DB is offline and so I am not able to see its properties.. Is this option anywhere else ?

    In Logs I can see one message "Setting DB option OFFLINE to ON for database (DBname)"

    Please help me. Thanks in advance.

  • In event logs before DB went offline I can see "Certificate Services clinet has been started succeffully" and than next log says "Services Stopped successfully" and I found thats data exchange services.

    Is it causing DB to go Offline ?

  • did you try to make it online ?

    Alter Database DBNAME set online

    also what is the status of the DB

    select * from sys.sysdatabases where name = 'DB NAME'

    did you checked Event Viewer logs as well?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • The status of DB is

    Status Status2

    66057 1627389952

    now where to check what this status does mean ?

    There is same thing in Eventlog too:

    12/06/2012 17:39:59,spid53,Unknown,Setting database option OFFLINE to ON for database DBname.

    12/06/2012 17:39:59,spid53,Unknown,Starting up database 'DBNAME'.

  • Using

    Select * from sys.databases

    I came to know that is_auto_close_on is set to 1 (True) that means it will try to get up automatically right ?

    but unfortunately it was not. (:-

  • did you try this ?

    Alter database DB_Name set online

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • Yes it will make my db online.

    But I want to know reason why it was offline.

  • I came to know that is_auto_close_on is set to 1 (True) that means it will try to get up automatically right ?

    if Auto_close is False, Sql server just close the connection to the DB if there is no work

    Seems someone manually made it offline

    12/06/2012 17:39:59,spid53,Unknown,Setting database option OFFLINE to ON for database DBname.

    12/06/2012 17:39:59,spid53,Unknown,Starting up database 'DBNAME'.

    any chance, you can check dbcc inputbuffer (53) , and which user who run it ?

    check sp_who2 59

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • Hello Saket,

    just for my knowledge,

    What does that status means ?

    Status 66057

    Status2 1627389952

    From where I can check the meaning of this status ?

  • i'm still finding meaning of the status no,

    Seems there is no information about it on Microsoft document

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • jitendra.padhiyar (12/7/2012)


    But I want to know reason why it was offline.

    Somebody connected to the server and ran ALTER DATABASE ... SET OFFLINE

    SQL doesn't set DBs offline by itself. What you saw in the error log was the report when someone (or perhaps a job) altered the database and set it offline. Ask around, see who has sufficient rights to do that (DB owner or sysadmin I think), check in the default trace.

    The statuses are not worth looking at, they're only in sysdatabases which is deprecated and only included for backward compatibility with SQL 2000. Use sys.databases instead, all the various states are broken out into individual columns in the new (well 7 year old) view.

    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
  • hey please find below link for auto_close

    http://technet.microsoft.com/en-us/library/bb402929.aspx

    also in case your missing mdf or ldf missing .

  • Hemant.R (12/10/2012)


    also in case your missing mdf or ldf missing .

    Missing files will not result in the DB being in the offline state. They result in the Recovery_Pending state if the DB cannot come online with those files missing.

    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

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

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