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 12345»»»

Database stuck "in recovery" Expand / Collapse
Author
Message
Posted Tuesday, June 22, 2010 8:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, 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.
Post #941103
Posted Tuesday, June 22, 2010 9: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 @ 6:12 AM
Points: 42,836, Visits: 35,966
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 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 #941126
Posted Tuesday, June 22, 2010 9: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 @ 6:12 AM
Points: 42,836, Visits: 35,966
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 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 #941131
Posted Tuesday, June 22, 2010 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, 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.

Post #941150
Posted Tuesday, June 22, 2010 9:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, 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.
Post #941152
Posted Tuesday, June 22, 2010 9:51 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 @ 6:12 AM
Points: 42,836, Visits: 35,966
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 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 #941161
Posted Tuesday, June 22, 2010 9:53 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 @ 6:12 AM
Points: 42,836, Visits: 35,966
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 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 #941162
Posted Tuesday, June 22, 2010 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, 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.
Post #941239
Posted Tuesday, June 22, 2010 12:09 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 @ 6:12 AM
Points: 42,836, Visits: 35,966
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 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 #941268
Posted Tuesday, June 22, 2010 12:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, 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.
Post #941270
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse