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 8:52 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
HI,

We have a database that the status is set to Shutdown. How can I change the DB Status to Normal.

Thanks,
Post #1192912
Posted Wednesday, October 19, 2011 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 12:26 PM
Points: 55, Visits: 136
Where are you checking the status of the database. As per BOL, a database can have one of the following states.
online, offline, restoring, recovering, recovery pending, suspect and emergency.

what is the result of the below query.
select state_desc from sys.databases where name='<your database name>'
Post #1192918
Posted Wednesday, October 19, 2011 11:15 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
Hi,

After running the query you provided, the output is: RECOVERY_PENDING.

I'm seeing the Shutdown status at the General page of the DB Property section. In the Database subsection, the Status of the DB is Shutdown. In looking at other DBs, they have Normal Status.
Post #1193055
Posted Wednesday, October 19, 2011 11:18 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:36 AM
Points: 42,488, Visits: 35,556
Please open the SQL error log and find all errors relating to that database.

Got a 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 #1193058
Posted Wednesday, October 19, 2011 12:27 PM
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
No, unfortunately, I don't have a backup file. Below is an error message that I can see in the log.

2011-10-10 15:00:50.03 spid27s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2011-10-10 15:00:50.03 spid27s File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF" may be incorrect.

Post #1193127
Posted Wednesday, October 19, 2011 12:33 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:36 AM
Points: 42,488, Visits: 35,556
Another production database without a backup? Why?

Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\dbFile_log.LDF'


Is the file there?



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 #1193132
Posted Wednesday, October 19, 2011 12:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
EjSQLme (10/19/2011)
No, unfortunately, I don't have a backup file.
(facepalm) If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast.. Sorry man..

And the ULTRA important question already asked, why no backup?

CEWII
Post #1193142
Posted Wednesday, October 19, 2011 1:01 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:36 AM
Points: 42,488, Visits: 35,556
Elliott Whitlow (10/19/2011)
If this is an important database I hope you can fix it or your resume is up to date. I've seen businesses go under because of things like this and even if they don't the DBA is usually toast..


So far this month I'm 0-for-2 on corrupt databases without backups. Both the others were complete and total losses, no repair, no data could be extracted.



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 #1193157
Posted Wednesday, October 19, 2011 1:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Beyond the monitoring software I use, I have been configuring policies on all my SQL 2008 servers that check to be sure that all but tempdb have had a full backup within the last 25 hours. I haven't lost a database in a long time and even longer without a backup, I'd like to keep it that way.

I've seen a DBA get fired over stuff like this, the last case the DBA did have a backup, from a month ago which in most cases is as bad as not having one at all.. He got lax and even though the monitoring software was sending him "love notes" he didn't resolve the issue. I will give him some cover, he was getting a fair number of alerts but after so long you'd think he would have seen a no backups message..

Even with monitoring in place I will periodically go out to each server and run a script I have that tells me when every single database got its last full backup.

As a DBA a little paranoia goes a long way and some of my "paranoid code" has saved my bacon (and others) more than twice..

CEWII
Post #1193169
Posted Wednesday, October 19, 2011 1:45 PM
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, the file is there. This is a Dev DB, we have been doing some work on it that would take us a while to recreate. So what is my next step?
Post #1193195
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse