Database Status = In Recovery

Question

Database Status = In Recovery

mhilligas

Ten Centuries

Points: 1354
More actions
August 5, 2010 at 10:21 am

#227505

I have a database that is not functioning.
I said In Recovery after the database name, but after a refresh that went away.
When I try to get the properties the dialog says the status is Shutdown, Normal.
When I try to view the contents of a table I get:
Unspecified Error (MS Visual Database Tool)
Is there a fix? I have not delt with this before.
Any help would be great.
Michael Hilligas

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the exact status of the database? (query sys.databases)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mhilligas Ten Centuries Points: 1354 More actions · Answer 2

Thanks for the reply.

The status currently is (Shutdown, Normal)

which seems OK, but I cannot do anything with the database.

I just noticed my log file is 4.5 GB and this is SQL Express 🙁

I was going to try to truncate the log file but as i stated above

I cannot do anything with the DB.

I believe it may still be recovery. But I am not sure.

Regards

Michael Hilligas

mhilligas Ten Centuries Points: 1354 More actions · Answer 3

I got the status from the properties dialog of the database.

can you tell me how to query sys.databases.

sorry for my ignorance

Michael Hilligas

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Open a query window and write a select statement that queries the sys.databases view.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mhilligas Ten Centuries Points: 1354 More actions · Answer 5

I have the query open.

what column will tell me the actual status.

AGAIN.....Sorry for my ignorance

Michael Hilligas

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You could look in Books Online and check what the columns are. It's also pretty self-explanatory. The column is called status_desc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

p.s. Is there someone there who knows SQL? If there's something wrong with this database, fixing it will get complex and technical. Will likely need someone familiar with SQL Server

p.p.s. You do have backups? Right?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mhilligas Ten Centuries Points: 1354 More actions · Answer 8

The database is ONLINE

I was going to truncate the log file, but I cannot open a query against the DB

mhilligas Ten Centuries Points: 1354 More actions · Answer 9

I have done quite alot of SQL work, but have had little problems so my troubleshooting abilities are limited.

this database is a configuration database for a application program.

the application program quit working so the customer contacted me to see why.

I cannot find a backup for this database. this is both mine and the customers mistake.

I am sorry to take up your time.

is there someway to truncate the log file, as I think some of the issue may be that SQL Express can only handle a 4 GB database or am I wrong?

Thanks for your help.

Michael Hilligas

mhilligas Ten Centuries Points: 1354 More actions · Answer 10

GilaMonster...

I just ran the query again and it now says the DB is RECOVERING.

I imagine I should bide my time and see if it recovers.

Michael Hilligas

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Yes you should, but it shouldn't be switching from online to recovering.

Is auto_close enabled?

Don't touch the log file, at least not now. What's the recovery model? What's the data loss allowances for this DB? (I imagine large, seeing as there's no backups)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

mhilligas Ten Centuries Points: 1354 More actions · Answer 12

GilaMonster....

is_auto_close_on = 1

Recovery model is simple

Not sure how to check dataloss allowance

Thanks for helping me

Michael Hilligas

mhilligas Ten Centuries Points: 1354 More actions · Answer 13

GilaMonster...

I am the most blessed man on earth.

The DB came back online and my application can connect to the DB.

All the config data is there.

I think I will make a backup 🙂

Thanks so very much for your help.

Regards

Michael Hilligas

mhilligas Ten Centuries Points: 1354 More actions · Answer 14

WELL... I spoke to soon.

It has gone back into recovering.

I will let it go for a while and see.