SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database stuck "in recovery"


Database stuck "in recovery"

Author
Message
yessen
yessen
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
Hello,
I am new to this forum, but I figured there are a lot of experience dbas around here.

I have this problem. My .ldf file grew to enormous size and when I digged online I found that using "release unused space" in tasks->shrink->file
could solve the problem. Also someone advised to use: DUMP TRAN [database_name] WITH NO_LOG.

Then, when my system engineer try to use BACKUP EXEC program to back up the databases everyday, for some reason it makes the databases
go into "recovery mode".

I am trying to solve this issue by trying to understand why would they go into such mode and how to prevent them going into it.

Any suggestions or ideas will be greatly appreciated.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88034 Visits: 45277
Please query sys.databases. What's the exact value for state_desc for that database?
Look in the SQL error log. Are there any recent messages/errors for that database? If so, post them please.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88034 Visits: 45277
yessen (6/22/2010)
Also someone advised to use: DUMP TRAN [database_name] WITH NO_LOG.
\

Very bad advice. Please read through this - Managing Transaction Logs

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


yessen
yessen
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
It finished the "recovery mode" now and state says "ONLINE".

But everytime SQL Server needs to be restarted it goes into the same "recovery mode". I want to prevent this from happening and I can't because I have no idea why it happens first of all.
yessen
yessen
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
GilaMonster (6/22/2010)
yessen (6/22/2010)
Also someone advised to use: DUMP TRAN [database_name] WITH NO_LOG.
\

Very bad advice. Please read through this - Managing Transaction Logs


I read this article and understood the reason for transaction log file, however, i dont understand what is so bad about this dump tran query.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88034 Visits: 45277
Firstly DUMP is deprecated and will be removed in a future version of SQL. Replacement is backup. Truncate_only (as an option for Backup Log) is also deprecated and does not work in SQL 2008.

It breaks the log chain. Once you run that you will not be able to take log backups until a full or diff backup is taken. That leaves you open to data loss. If the DB is in full recovery then, I would assume, that point-in-time recovery is required. Breaking the log chain means you lose the ability to do point in time restores.

If you don't need to be able to restore the DB to a point in time, just set it to simple recovery model and SQL will auto truncate the log on a regular basis.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88034 Visits: 45277
yessen (6/22/2010)
But everytime SQL Server needs to be restarted it goes into the same "recovery mode". I want to prevent this from happening and I can't because I have no idea why it happens first of all.


It will. It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)

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


yessen
yessen
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
GilaMonster (6/22/2010)
until a full or diff backup is taken.


Wow you are making things a lot more clear to me now.

can you elaborate on full or diff backup?

If I do it, I will be able to restore to certain point in time again? I guess the point in time restoration allows you to restore to particular time and simple logging will prevent you from doing this and will allow only to restore when the last backup was made, is that right?

How does that command break the log chain? I don't understand that.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88034 Visits: 45277
yessen (6/22/2010)
can you elaborate on full or diff backup?


full database backup or differential database backup. Check books online if you want more details

I guess the point in time restoration allows you to restore to particular time and simple logging will prevent you from doing this and will allow only to restore when the last backup was made, is that right?


Correct. Again, look in books online for more details.

How does that command break the log chain? I don't understand that.

It discards log records without backing them up. Missing log records means no restore, plus log backups will fail after that command is run.

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


yessen
yessen
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 60
GilaMonster (6/22/2010)
yessen (6/22/2010)
can you elaborate on full or diff backup?


full database backup or differential database backup. Check books online if you want more details

I guess the point in time restoration allows you to restore to particular time and simple logging will prevent you from doing this and will allow only to restore when the last backup was made, is that right?


Correct. Again, look in books online for more details.

How does that command break the log chain? I don't understand that.

It discards log records without backing them up. Missing log records means no restore, plus log backups will fail after that command is run.


The only reason I had to discard log records is because prior DBA did not really care about the size of .ldf files and they grew to 300gb size, which was impossible to backup (took forever). Is there a good way to maintain .ldf size at certain size? I tried to set the MAXSIZE = 10gb but when it reached that size my database could no longer make transactions and I had to get rid of that limit by setting it back to -1. All I am trying to do to set it to certain size and still continue writing the most recent transactions.

When I do "full database backup or differential database backup", does that shrinks the .ldf size? From my observation, backup just allows you to come back to certain state, but does not affect .mdf or .ldf file sizes at all. I might be wrong though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search