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

Backup and Transaction Log Maintenance Expand / Collapse
Author
Message
Posted Monday, January 07, 2013 2:28 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:02 AM
Points: 28, Visits: 153
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
Posted Monday, January 07, 2013 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Posted Monday, January 07, 2013 3:01 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 11:09 AM
Points: 64, Visits: 1,202
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
Posted Monday, January 07, 2013 3:22 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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
Posted Monday, January 07, 2013 3:31 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:02 AM
Points: 28, Visits: 153
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
Posted Monday, January 07, 2013 3:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:02 AM
Points: 28, Visits: 153
Hi GilaMonster,

Going to have a look now. Thank you.
Post #1403516
Posted Monday, January 07, 2013 3:46 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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
Posted Monday, January 07, 2013 4:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:02 AM
Points: 28, Visits: 153
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
Posted Monday, January 07, 2013 4:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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
Posted Monday, January 07, 2013 5:28 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 »

Add to briefcase 12»»

Permissions Expand / Collapse