DB gone offline

Question

Post reply

DB gone offline

jitendra.padhiyar

SSCertifiable

Points: 5824
More actions
December 6, 2012 at 11:28 pm

#285967

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

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

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

sanket kokane Hall of Fame Points: 3465 More actions · Answer 1

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

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

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 2

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.

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 3

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 ?

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

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?

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

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 5

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'.

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 6

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. (:-

sanket kokane Hall of Fame Points: 3465 More actions · Answer 7

did you try this ?

Alter database DB_Name set online

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

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 8

Yes it will make my db online.

But I want to know reason why it was offline.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 9

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

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

jitendra.padhiyar SSCertifiable Points: 5824 More actions · Answer 10

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 ?

sanket kokane Hall of Fame Points: 3465 More actions · Answer 11

i'm still finding meaning of the status no,

Seems there is no information about it on Microsoft document

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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

Hemant.R Ten Centuries Points: 1286 More actions · Answer 13

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 .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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