DB gone offline

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » DB gone offline

15 posts, Page 1 of 2

1 2 »»

DB gone offline

Rate Topic

Display Mode

Topic Options

Author

Message

jitendra.padhiyar

Posted Thursday, December 06, 2012 11:28 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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

Post #1393872

sanket kokane

Posted Thursday, December 06, 2012 11:46 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

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

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

Post #1393881

jitendra.padhiyar

Posted Thursday, December 06, 2012 11:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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.

Post #1393885

jitendra.padhiyar

Posted Thursday, December 06, 2012 11:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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 ?

Post #1393887

sanket kokane

Posted Friday, December 07, 2012 12:02 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

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?

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

Post #1393889

jitendra.padhiyar

Posted Friday, December 07, 2012 12:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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

Post #1393893

jitendra.padhiyar

Posted Friday, December 07, 2012 12:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

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

Post #1393897

sanket kokane

Posted Friday, December 07, 2012 12:35 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

did you try this ?

Alter database DB_Name set online

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

Post #1393899

jitendra.padhiyar

Posted Friday, December 07, 2012 12:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:09 AM
Points: 167, Visits: 440

Yes it will make my db online.

But I want to know reason why it was offline.

Post #1393900

sanket kokane

Posted Friday, December 07, 2012 12:56 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

[b]

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

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

Post #1393904

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions