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


Backup and Transaction Log Maintenance


Backup and Transaction Log Maintenance

Author
Message
craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 177
Good Morning Everyone,

I hope everyone is having a nice Monday morning Crying I came into work today and I noticed something while checking my backups and I was wondering if I could get some advice. We currently have two jobs running deleting records from two tables every hour to prune back our database, the problem I have is that the transaction logs are now the same size as the .bak's. I know this is as a result of deleting the records, and we are deleting the minimum number we can to reduce the database over a long period. My question is more to do with shrinking the logs and what would be the best way to go about it? Or is this even the best approach?

Any help would be greatly appreciated especially considering its Monday, and nobody wants to get into this stuff on a Monday Crazy haha

Thanks Everyone,
Craig
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33631 Visits: 16649
Craig

Don't shrink the logs, but make sure you're backing them up regularly. Search the web for "SQL Server Transaction Log Maintenance" for more information.

John
_Beetlejuice
_Beetlejuice
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 1301
I would not recommend shrinking the transaction log as the log will probably grow again.
How often are you performing your transaction log backups?
You could increase the amount of transaction log backups, this would reduce the size of the transaction log backups and may also help prevent the transaction log growing
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217333 Visits: 46278
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


craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 177
Hi John, Hi BeetleJuice,

Thanks for getting back to me so quickly :-) I had a look over a few topics on your advice John and I seem to be doing things as advised. I have a backup maintenance plan taking my transaction log backups once daily (beetlejuice) and I take a full backup once weekly. Would it be advised to take them more regularly?

Thanks again,
Craig
craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 177
Hi GilaMonster,

Going to have a look now. Thank you.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217333 Visits: 46278
craig.dixon (1/7/2013)
Would it be advised to take them more regularly?


Yes. Daily log backups mean that you can lose up to 24 hours data in the case of a disaster. If that's acceptable, consider simple recovery model and daily differential backups. If that's not acceptable, then you need log backups to match your data loss allowance.

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


craig.dixon
craig.dixon
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 177
Hi GilaMonster,

Thanks for the advice, I read through the article you recommended to see if I could work out a solution and I think I mite be getting somewhere. Im just wondering though, if I was to use simple recovery mode and differential backups, would the differential backups not be as large as my transaction logs? I thought that as its the two jobs I have deleting records causing the issue I would encounter the same problem?

Thanks again for your help, I really appreciate it,
Craig
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95801 Visits: 33013
Differentials only capture data changes since the last full backup. And, differentials won't help you with the logs. The best answer is to follow Gail's advice and run more frequent log backups.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217333 Visits: 46278
craig.dixon (1/7/2013)
Im just wondering though, if I was to use simple recovery mode and differential backups, would the differential backups not be as large as my transaction logs?


Maybe, maybe not. Depends what changes you do.

Log backups record each and every change (the operations that make the changes). Differential backups record the net result of those changes (the data after)

The major difference would be in the size of your log files, as they would no longer need to store 24 hours of log records.

Honestly though, the decision which should not be based on 'which is larger', but on 'which allows me to meet my SLAs'. If you have a max 2 hour data loss SAL, then your current backup strategy does not support it, neither does the switch to simple recovery and diff backups option.

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


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