<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Backup and Transaction Log Maintenance
12 posts, Page 1 of 2
1
2
»»
Backup and Transaction Log Maintenance
Rate Topic
Display Mode
Topic Options
Author
Message
craig.dixon
craig.dixon
Posted Monday, January 07, 2013 2:28 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24,
Visits: 91
Good Morning Everyone,
I hope everyone is having a nice Monday morning
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
haha
Thanks Everyone,
Craig
Post #1403493
John Mitchell-245523
John Mitchell-245523
Posted Monday, January 07, 2013 2:59 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 9:14 AM
Points: 4,428,
Visits: 7,196
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
Post #1403503
_Beetlejuice
_Beetlejuice
Posted Monday, January 07, 2013 3:01 AM
Valued Member
Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:28 AM
Points: 62,
Visits: 1,140
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
Post #1403504
GilaMonster
GilaMonster
Posted Monday, January 07, 2013 3:22 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 11:48 PM
Points: 37,723,
Visits: 29,979
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 #1403513
craig.dixon
craig.dixon
Posted Monday, January 07, 2013 3:31 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24,
Visits: 91
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
Post #1403515
craig.dixon
craig.dixon
Posted Monday, January 07, 2013 3:32 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24,
Visits: 91
Hi GilaMonster,
Going to have a look now. Thank you.
Post #1403516
GilaMonster
GilaMonster
Posted Monday, January 07, 2013 3:46 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 11:48 PM
Points: 37,723,
Visits: 29,979
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 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 #1403522
craig.dixon
craig.dixon
Posted Monday, January 07, 2013 4:22 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24,
Visits: 91
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
Post #1403530
Grant Fritchey
Grant Fritchey
Posted Monday, January 07, 2013 4:46 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 13,380,
Visits: 25,164
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1403542
GilaMonster
GilaMonster
Posted Monday, January 07, 2013 5:28 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 11:48 PM
Points: 37,723,
Visits: 29,979
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 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 #1403557
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
