Managing Transaction Logs

Question

Managing Transaction Logs

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

MostInterestingMan (10/6/2010)
So when the diff backups occur is the log not backup at the time as well?

No.

Full and diff backups contain only enough of the log to be able to restore to a consistant state. Nothing more. They do not truncate the transaction log. They do not give you any form of point-in-time recovery.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

MostInterestingMan (10/6/2010)
Understood. If we were about to move to simple recovery model will the current backup strategy hold? Weekly Full and nightly Diffs.

Yes.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

[/quote]

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.[/quote]

Okay. We are meeting tomorrow and I will proceed as you have advised. I will read what Gail has suggested as well and keep you guys posted after my meeting tomorrow. Thanks for all your help - really appreciated.

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 4

If you want to add more weight to it talk about management of virtual log files. Google/Bing "VLF" and "SQLSkills". Kimberley and Paul have some great blogs on the subject. The chances are that with no log backups your log file will have grown significantly and depending on the autogrow increment setting you could find your performance seriously hampered.

Hope this helps,
Rich

[p]
[/p]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

RichardDouglas (10/6/2010)
If you want to add more weight to it talk about management of virtual log files. Google/Bing "VLF" and "SQLSkills". Kimberley and Paul have some great blogs on the subject. The chances are that with no log backups your log file will have grown significantly and depending on the autogrow increment setting you could find your performance seriously hampered.

Especially if you're shrinking the log, then letting it grow, then shrinking the log, then letting it grow, .....

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

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 6

GilaMonster (10/6/2010)
RichardDouglas (10/6/2010)
If you want to add more weight to it talk about management of virtual log files. Google/Bing "VLF" and "SQLSkills". Kimberley and Paul have some great blogs on the subject. The chances are that with no log backups your log file will have grown significantly and depending on the autogrow increment setting you could find your performance seriously hampered.
Especially if you're shrinking the log, then letting it grow, then shrinking the log, then letting it grow, .....

oh god, great point Gail, you might want to check your index fragmentation too. Paul Randall did a great demo on how shrinking your files causes fragmentation.

Hope this helps,
Rich

[p]
[/p]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

RichardDouglas (10/6/2010)
oh god, great point Gail, you might want to check your index fragmentation too. Paul Randall did a great demo on how shrinking your files causes fragmentation.

That's only if you're shrinking data files. Shrinking log files doesn't cause index fragmentation.

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

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 8

GilaMonster (10/6/2010)
RichardDouglas (10/6/2010)
oh god, great point Gail, you might want to check your index fragmentation too. Paul Randall did a great demo on how shrinking your files causes fragmentation.
That's only if you're shrinking data files. Shrinking log files doesn't cause index fragmentation.

As usual you're quite right, I should have clarified that a bit better!

Hope this helps,
Rich

[p]
[/p]

SteveBell SSCrazy Points: 2008 More actions · Answer 9

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?

Francis,

I got a whole lot of help from Gail and the others in this forum when I posted a somewhat similar question

http://www.sqlservercentral.com/Forums/Topic983007-1291-1.aspx

We are instituting significant changes to what has been going on here (which is somewhat similar to your situation). Most of it was just a matter of educating the affected parties (including a lesson on why RAID 1 is not a backup strategy for transaction logs).

I'm finding that although the business units may have originally said "point-in-time-recovery is not required" they'll be screaming if they actually lose a day's worth of data.

My point is, when I followed the advice I got here and took it to senior management, things started to happen VERY quickly.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

SwayneBell (10/7/2010)
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?
I'm finding that although the business units may have originally said "point-in-time-recovery is not required" they'll be screaming if they actually lose a day's worth of data.
My point is, when I followed the advice I got here and took it to senior management, things started to happen VERY quickly.

That's why I was hammering the same question over and over again.

I once worked for a small mom and pop shop. The tlog was growing "only" by 200 mb a day.

That 200 mb growth took 10 full time jobs to input all that data in the system (that's well over 2000$ worth of time).

So you're still talking about 7 GB of tlog which I still think you are reffering to the db log file size rather than the daily tlog backup filesize (without reindexing). But assuming the same data value, you're talking about 70 000$ worth of data... definitly not a small thing.

homebrew01 SSC Guru Points: 55548 More actions · Answer 11

Maybe I missed it, but why haven't you put t-log backups in place as suggested ? If it's a production database, I don't think that pushing for simple recovery is the best option even though they say they are fine with 24 hour data loss. Better to have FULL with log backups. Then 6 months from now when there's a problem, you'll be in a good position to recover.

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 12

GilaMonster (10/6/2010)
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

homebrew01 (10/7/2010)
Maybe I missed it, but why haven't you put t-log backups in place as suggested ? If it's a production database, I don't think that pushing for simple recovery is the best option even though they say they are fine with 24 hour data loss. Better to have FULL with log backups. Then 6 months from now when there's a problem, you'll be in a good position to recover.

My meeting was okay and I was given the greenlight to implement some best practices with the backup startegy. Firstly let me thank all of you for your time and all the great advise. I read Gail's piece on "managing trans logs" (twice) and came away with a ton of knowledge. I also read the forum posting where Gail ranted about this issue...Below is what I have decided so please charm in if you can...

Recovery model set to FULL:

Backup Strategy Managed by Data Center (out of my hands):

1) Full Weekly Backups (Wed's 1:00 AM)

2) Nightly Diff Backup (1:00 AM except Wed's when the Full (base) runs)

Transaction Log Management (locally) by me:

1) Hourly log backup

-> starting tonight after the Diff backup runs (concept of log chains)

For the DB with 7GB log I am not doing any log backup until I investigation why the log grew to 7GB in the first place. I have a pending discussion with the developer to understand the app for some background.

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

MostInterestingMan (10/7/2010)
For the DB with 7GB log I am not doing any log backup until I investigation why the log grew to 7GB in the first place. I have a pending discussion with the developer to understand the app for some background.

Then set it to Simple recovery in the meantime.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

MostInterestingMan (10/7/2010)
GilaMonster (10/6/2010)
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
homebrew01 (10/7/2010)
Maybe I missed it, but why haven't you put t-log backups in place as suggested ? If it's a production database, I don't think that pushing for simple recovery is the best option even though they say they are fine with 24 hour data loss. Better to have FULL with log backups. Then 6 months from now when there's a problem, you'll be in a good position to recover.
My meeting was okay and I was given the greenlight to implement some best practices with the backup startegy. Firstly let me thank all of you for your time and all the great advise. I read Gail's piece on "managing trans logs" (twice) and came away with a ton of knowledge. I also read the forum posting where Gail ranted about this issue...Below is what I have decided so please charm in if you can...
Recovery model set to FULL:
Backup Strategy Managed by Data Center (out of my hands):
1) Full Weekly Backups (Wed's 1:00 AM)
2) Nightly Diff Backup (1:00 AM except Wed's when the Full (base) runs)
Transaction Log Management (locally) by me:
1) Hourly log backup
-> starting tonight after the Diff backup runs (concept of log chains)
For the DB with 7GB log I am not doing any log backup until I investigation why the log grew to 7GB in the first place. I have a pending discussion with the developer to understand the app for some background.
Thanks

For the actual used space in the log you can run this :

dbcc sqlperf(logspace)

That'll give you the hd used space and the % used space in that file.

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 15

[/quote]Then set it to Simple recovery in the meantime.[/quote]

[/quote]

For the actual used space in the log you can run this :

dbcc sqlperf(logspace)

That'll give you the hd used space and the % used space in that file.[/quote]

7 GB log DB set to SIMPLE.

Thanks for the DBCC command.

For my maintenance plan, is this the recommended best practice?

1) nightly dbcc check for user databases (before backups)

2) monthly selective index maintenance (reorg / rebuild based on frag)

-> reorg for avg_fragmentation_in_percent < 30

-> rebuild for avg_fragmentation_in_percent > 30

Do you run nightly integrity check on sys DBs? How about index maintenace?

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends