Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DB Offline Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 9:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 214, Visits: 853
Could this have happened following a reboot of the Windows box hosting SQL Server?
Post #1514362
Posted Thursday, November 14, 2013 9:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 43,026, Visits: 36,191
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 2008, MVP
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

Post #1514366
Posted Thursday, November 14, 2013 1:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 287, Visits: 1,151
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.
Post #1514465
Posted Monday, November 18, 2013 12:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Thanks Gail
Post #1515110
Posted Tuesday, November 19, 2013 8:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1515872
Posted Wednesday, November 20, 2013 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 287, Visits: 1,151
Why do you need exact syntax here?

It wont give you exact T-SQL and i don't think it's needed in your case. If it says Type as "database" and DDL Operation as ALTER in the report, then some one ran ALTER Database stmnt. And it also gives you exact time and the Login Name in this report. Now you can verify in your error logs at the exact same time, it logs something similar to "Setting database Option Offline to ON for database......."
Post #1516073
Posted Sunday, November 24, 2013 11:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Thanks Sreekanth. I had misinteptreted log entry "Offline to ON" as db has been made online :).
Post #1517160
Posted Tuesday, November 26, 2013 5:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:41 AM
Points: 39, Visits: 275
I figure this is sorted... but just check that auto close is not set to ON.
Post #1517625
Posted Tuesday, November 26, 2013 6:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 43,026, Visits: 36,191
Zksod (11/26/2013)
I figure this is sorted... but just check that auto close is not set to ON.


Autoclose doesn't set the database to the OFFLINE state. A database closed by Auto_Close remains in the ONLINE state and just has the is_cleanly_shutdown column set to 1 in sys.databases.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1517658
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse