October 6, 2010 at 1:20 pm
Hi,
I have the below listed maintenance plan in place and is seeking some advise on how to manage transaction logs. The data loss risk exposure is 24 hours: The database recovery model is FULL (THIS IS PER REQUIREMENT - IT MUST BE FULL).
1) nightly dbcc check for user databases
2) monthly selective index maintenance (reorg / rebuild based to frag)
-> only for page_count > 100 (per req.)
-> reorg for avg_fragmentation_in_percent < 30
-> rebuild for avg_fragmentation_in_percent > 30
3) monthly update stats after index maintenance occurs
4) full backups once weekly
5) diff backup nightly - the weekly full is the base
With the recovery model set at FULL (USING SIMPLE IS NOT AN OPTION) and no log backup being done what is the recommended best practice on managing the transaction log?
Today I am running this script immediately after the nightly backup completes:
DECLARE @dbname nvarchar(50)
SELECT @dbname = RTRIM(@dbname)
SELECT 'BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY'
EXEC ('DBCC SHRINKDATABASE (' + @dbname + ',TRUNCATEONLY)')
Is this method a best practice approach? Also when the weekly Full backup completes does it backup the log? What about the nightly Diff backup, does that backup the transaction log when completed? Can I run the log backup script above anytime in the day if for example some of the logs are growing too large?
Finally, if you look at my maintenance plan above I am not doing anythig for the system databases now. What type of maintenance, if any, is recommended for system databases? NIghtly? Weekly?
Thanks for your time and I really appreciate any assistance.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
October 6, 2010 at 1:22 pm
How big is the DB and how big are the tlog after "normal" daily growth?
October 6, 2010 at 1:24 pm
Why are you using full if you don't plan on taking transaction log backups?
I realize you said it's a requirement, but why? That sounds like it was a rule written by someone who doesn't know what the transaction log is.
October 6, 2010 at 1:26 pm
Ninja's_RGR'us (10/6/2010)
How big is the DB and how big are the tlog after "normal" daily growth?
The databases are small now and I have not monitor the "normal" daily log growth. However I notice several days ago that we had a log file of 7GB.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
October 6, 2010 at 1:27 pm
Derrick Smith (10/6/2010)
Why are you using full if you don't plan on taking transaction log backups?I realize you said it's a requirement, but why? That sounds like it was a rule written by someone who doesn't know what the transaction log is.
You are right? The thing is I can't get them to revist that decision - at least for now.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
October 6, 2010 at 1:32 pm
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
How big is the DB and how big are the tlog after "normal" daily growth?The databases are small now and I have not monitor the "normal" daily log growth. However I notice several days ago that we had a log file of 7GB.
Hmm I wasn't clear in my questions.
Is the log file on the drive 7 GB?
Or is the tlog backup file 7GB?
Is that tlog backup after a reindex job or simply after normal use of the system?
Who are "they"? And why the heck are they not doing it themselves if "they" know what "they" want?
Also 7 GB is not small by any means, so losing 7 GB of changes in the DB seems like a lot, especially if that's all done by hand.
October 6, 2010 at 1:45 pm
Ninja's_RGR'us (10/6/2010)
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
How big is the DB and how big are the tlog after "normal" daily growth?The databases are small now and I have not monitor the "normal" daily log growth. However I notice several days ago that we had a log file of 7GB.
Hmm I wasn't clear in my questions.
Is the log file on the drive 7 GB?
Or is the tlog backup file 7GB?
Is that tlog backup after a reindex job or simply after normal use of the system?
Who are "they"? And why the heck are they not doing it themselves if "they" know what "they" want?
Also 7 GB is not small by any means, so losing 7 GB of changes in the DB seems like a lot, especially if that's all done by hand.
One of the Tlog file got to 7gb but the drive is bigger.
After normal use of the system - also the 7gb was a log file growth before I backed it up.
I guess my root question is with a full recovery model and one weekly full backup, nightly diffs backup, what is the best way to deal with the tlogs? Is my method about the best practice?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
October 6, 2010 at 1:52 pm
The method you're trying to implement is a million miles away from Best Practice actually. The root issue here is that the database is on Full recovery when there is absolutely no reason for it to be. Any remedy other than changing that will be just a band-aid.
You might as well just go along with it and start taking txn log backups every 15 minutes or every hour, and then you have the option to do point in time recovery.
October 6, 2010 at 1:56 pm
I still don't think you understand my question.
When you do backup log to file...
What's the size of that file?
My questions is not about missing HD space on the drive but the lost data between full backups.
Also Derrick is fully right. Either take log backups every x minutes or move to simple.
If you truly can lose 24H of data, then don't bother to backup logs daily other than to checkpoint and keep the file size in check.
October 6, 2010 at 1:59 pm
MostInterestingMan (10/6/2010)
With the recovery model set at FULL (USING SIMPLE IS NOT AN OPTION) and no log backup being done what is the recommended best practice on managing the transaction log?
Start doing log backups. Not as a best practice, as a requirement. Your method is pretty much worst practice, so far from even acceptable that it's a clear sign that there are serious problems of some form. In fact, I've already ranted twice today about people truncating their logs.
Please read through this - Managing Transaction Logs[/url]
If you are truncating your log after the backup, you are running in Simple recovery in all but name.
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
October 6, 2010 at 1:59 pm
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
How big is the DB and how big are the tlog after "normal" daily growth?The databases are small now and I have not monitor the "normal" daily log growth. However I notice several days ago that we had a log file of 7GB.
Hmm I wasn't clear in my questions.
Is the log file on the drive 7 GB?
Or is the tlog backup file 7GB?
Is that tlog backup after a reindex job or simply after normal use of the system?
Who are "they"? And why the heck are they not doing it themselves if "they" know what "they" want?
Also 7 GB is not small by any means, so losing 7 GB of changes in the DB seems like a lot, especially if that's all done by hand.
One of the Tlog file got to 7gb but the drive is bigger.
After normal use of the system - also the 7gb was a log file growth before I backed it up.
I guess my root question is with a full recovery model and one weekly full backup, nightly diffs backup, what is the best way to deal with the tlogs? Is my method about the best practice?
Point-in-time recovery is not required. The business is okay with 24 exposure. Which honestly is the disconnect with the decision to use full recovery model. So when the diff backups occur is the log not backup at the time as well?
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
October 6, 2010 at 2:03 pm
Differential backups have nothing to do with log backups.
October 6, 2010 at 2:12 pm
A couple of related threads from earlier today:
http://www.sqlservercentral.com/Forums/Topic999051-360-1.aspx
http://www.sqlservercentral.com/Forums/Topic981205-24-1.aspx
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
October 6, 2010 at 2:15 pm
MostInterestingMan (10/6/2010)
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
MostInterestingMan (10/6/2010)
Ninja's_RGR'us (10/6/2010)
How big is the DB and how big are the tlog after "normal" daily growth?The databases are small now and I have not monitor the "normal" daily log growth. However I notice several days ago that we had a log file of 7GB.
Hmm I wasn't clear in my questions.
Is the log file on the drive 7 GB?
Or is the tlog backup file 7GB?
Is that tlog backup after a reindex job or simply after normal use of the system?
Who are "they"? And why the heck are they not doing it themselves if "they" know what "they" want?
Also 7 GB is not small by any means, so losing 7 GB of changes in the DB seems like a lot, especially if that's all done by hand.
One of the Tlog file got to 7gb but the drive is bigger.
After normal use of the system - also the 7gb was a log file growth before I backed it up.
I guess my root question is with a full recovery model and one weekly full backup, nightly diffs backup, what is the best way to deal with the tlogs? Is my method about the best practice?
Point-in-time recovery is not required. The business is okay with 24 exposure. Which honestly is the disconnect with the decision to use full recovery model. So when the diff backups occur is the log not backup at the time as well?
Here's an easy solution. Move to simple recevory and send this thread as backup.
You won't find much better advice than whatever Gail can give you, or derreck, or mine.
If they still don't change their mind ask where they are getting that idea from and we'll go from there.
HTH.
October 6, 2010 at 2:18 pm
GilaMonster (10/6/2010)
MostInterestingMan (10/6/2010)
With the recovery model set at FULL (USING SIMPLE IS NOT AN OPTION) and no log backup being done what is the recommended best practice on managing the transaction log?Start doing log backups. Not as a best practice, as a requirement. Your method is pretty much worst practice, so far from even acceptable that it's a clear sign that there are serious problems of some form. In fact, I've already ranted twice today about people truncating their logs.
Please read through this - Managing Transaction Logs[/url]
If you are truncating your log after the backup, you are running in Simple recovery in all but name.
Understood. If we were about to move to simple recovery model will the current backup strategy hold? Weekly Full and nightly Diffs.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply