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 ««123»»

Change Database Status from Shutdown to Normal Expand / Collapse
Author
Message
Posted Wednesday, October 19, 2011 1:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
I'd probably go with a detach/re-attach, then you could specify all the file locations. Gail? Your thoughts?

CEWII
Post #1193201
Posted Wednesday, October 19, 2011 2:15 PM


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 @ 1:14 PM
Points: 40,615, Visits: 37,081
No. Never ever detach a suspect or recovery_pending database as it will not always reattach (suspect will never reattach)

Are you absolutely sure that file is there? Exact path, exact name.
Was there a drive problem that the drive came online later than SQL? If so, try restarting SQL Server.



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 #1193216
Posted Wednesday, October 19, 2011 2:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:13 PM
Points: 85, Visits: 524
Is the file there in the location showing in the error logs.
Try to detach and reattach providing the new path.
Post #1193243
Posted Wednesday, October 19, 2011 3:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 20,860, Visits: 32,886
R_S (10/19/2011)
Is the file there in the location showing in the error logs.
Try to detach and reattach providing the new path.


Read Gail's post just above.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1193254
Posted Friday, October 21, 2011 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 4, 2012 9:00 PM
Points: 102, Visits: 431
Yes, I'm sure that the file is there b/c I checked it to make sure it is there when I saw the error message that I posted. I believe there was an issue with the server and did crash, but we have other DBs on the same SQL Server, and this is the only DB with this issue.
Post #1194417
Posted Friday, October 21, 2011 8:20 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
Try taking the affected DB offline and then online (alter database ... set offline, alter database ... set online)

If that doesn't work, post any errors.



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 #1194422
Posted Friday, October 21, 2011 9:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
GilaMonster (10/19/2011)
No. Never ever detach a suspect or recovery_pending database as it will not always reattach (suspect will never reattach)

Are you absolutely sure that file is there? Exact path, exact name.
Was there a drive problem that the drive came online later than SQL? If so, try restarting SQL Server.
I agree it won't always re-attach.. If all the files are there and in the exact same place then a restart probably won't hurt. If the files have moved I don't see much of a choice.. Luckily I don't have to deal with this kind of problem often..

CEWII
Post #1194496
Posted Friday, October 21, 2011 9:35 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
Elliott Whitlow (10/21/2011)
If the files have moved I don't see much of a choice..


Move them back where they should be and offline/online or ALTER DATABASE ... MOVE then offline/online. Or restart SQL instead of the offline/online combo if there's nothing else running on the server.

I would be very, very cautious about detaching a database in anything other than Online state as if it doesn't reattach the situation's just got worse.



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 #1194504
Posted Friday, October 21, 2011 10:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
GilaMonster (10/21/2011)
Elliott Whitlow (10/21/2011)
If the files have moved I don't see much of a choice..


Move them back where they should be and offline/online or ALTER DATABASE ... MOVE then offline/online. Or restart SQL instead of the offline/online combo if there's nothing else running on the server.

I would be very, very cautious about detaching a database in anything other than Online state as if it doesn't reattach the situation's just got worse.
Provided moving them back is possible I would agree. My thought is damaged drive. But I agree with your point.

CEWII
Post #1194532
Posted Friday, October 21, 2011 10:25 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
If the drive is damaged you're probably restoring from backup.


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 #1194545
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse