Change Database Status from Shutdown to Normal

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Change Database Status from Shutdown to...

25 posts, Page 1 of 3

1 2 3 »»»

Change Database Status from Shutdown to Normal

Rate Topic

Display Mode

Topic Options

Author

Message

EjSQLme

Posted Wednesday, October 19, 2011 8:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 04, 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

cindyaz

Posted Wednesday, October 19, 2011 8:59 AM

Valued 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

EjSQLme

Posted Wednesday, October 19, 2011 11:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 04, 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

GilaMonster

Posted Wednesday, October 19, 2011 11:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

EjSQLme

Posted Wednesday, October 19, 2011 12:27 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 04, 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

GilaMonster

Posted Wednesday, October 19, 2011 12:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Elliott Whitlow

Posted Wednesday, October 19, 2011 12:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

GilaMonster

Posted Wednesday, October 19, 2011 1:01 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Elliott Whitlow

Posted Wednesday, October 19, 2011 1:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

EjSQLme

Posted Wednesday, October 19, 2011 1:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 04, 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 »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions