June 22, 2010 at 8:38 am
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.
June 22, 2010 at 9:18 am
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
June 22, 2010 at 9:20 am
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[/url]
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
June 22, 2010 at 9:41 am
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.
June 22, 2010 at 9:43 am
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[/url]
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.
June 22, 2010 at 9:51 am
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
June 22, 2010 at 9:53 am
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
June 22, 2010 at 11:15 am
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.
June 22, 2010 at 12:09 pm
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
June 22, 2010 at 12:15 pm
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.
June 22, 2010 at 12:20 pm
yessen (6/22/2010)
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.
Please read through the article I referenced.
To keep the log from growing, you need regular log backups. Don't limit the size of the ldf, you'll cause problems if you underestimate how big it needs to be.
When I do "full database backup or differential database backup", does that shrinks the .ldf size?
No.
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
June 23, 2010 at 7:24 am
GilaMonster (6/22/2010)
yessen (6/22/2010)
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.Please read through the article I referenced.
To keep the log from growing, you need regular log backups. Don't limit the size of the ldf, you'll cause problems if you underestimate how big it needs to be.
When I do "full database backup or differential database backup", does that shrinks the .ldf size?
No.
I ran the full backup on two servers that were in the "recovery mode" for 30 minutes each everytime I restarted my SQL Server and then switched to simple logging. Then I tried restarting the SQL server and they still were stuck in "recovery mode" for 30 minutes each.
I thought making the "full backup" should have restarted the "log chain" :/ please correct me if I am wrong.
June 23, 2010 at 7:39 am
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.
Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.
As I mentioned earlier
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
June 24, 2010 at 12:28 pm
GilaMonster (6/23/2010)
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.
As I mentioned earlier
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)
so in order to to make my database not stuck "in recovery mode" every time I restart my server, I need to back up my database and restore it from the same .bak file. This will restart my log chain and "recovery mode" should be quick after that. Am I right?
June 24, 2010 at 5:06 pm
No. Log chain and restart recovery are UNRELATED.
An intact log chain gives you the ability to restore a database to a particular point in time, in the case of a disaster (eg a disk failure)
Restart recovery (databases 'in recovery') will run every time a database starts up and it cannot be prevented. It's essential for SQL to run it always.
They are totally different concepts, totally different reasons, totally different operations.
There are several reasons why you may have a slow restart recovery. None are linked to 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
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply