Large tran log dump after Differential or Full backup

Question

Large tran log dump after Differential or Full backup

suedunham2

SSC Rookie

Points: 30
More actions
November 24, 2014 at 5:18 am

#313394

I am puzzled by the size of the first Monday tran log dump (we only run them Monday to Friday). I run a full backup nightly and that includes one after the maintplan over the weekend (rebuild index, reorganize index, shrink db, update statistics). Why is the first Monday tran log dump so large? Is there a flaw in the strategy?

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

The first log backup on Monday is so large because it contains all the log records since Friday afternoon, including all those index rebuilds.

p.s. Stop shrinking your database!!! If you're doing it after the index rebuilds, you're completely undoing all the work that the rebuilds did. If you're doing it before the rebuilds, the rebuilds will have to grow the database again.

p.p.s Rebuilding and reorganising is just duplicating work.

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

suedunham2 SSC Rookie Points: 30 More actions · Answer 2

Thank you for that prompt reply - it looks like there is no way of reducing the size of the first Monday tran log dump unless I change the database from full to simple recovery mode for the duration of the maint plan. Is that something you would recommend?

Thanks also for the helpful PS! I did not set up the maintenance plan and can see that whoever did has not made good choices. Is it right that if I set it to rebuild that would be enough (because it renders reorganise and upd stats redundant)? The largest database that we are managing is Sharepoint, I have heard that the tables are prone to fragmentation - is rebuild the best choice here too?

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

suedunham2 (11/24/2014)
Thank you for that prompt reply - it looks like there is no way of reducing the size of the first Monday tran log dump unless I change the database from full to simple recovery mode for the duration of the maint plan. Is that something you would recommend?

Absolutely NOT!

That's throwing away your point in time recovery (the entire reason for being in full recovery) and renders you vulnerable to data loss if there's a problem.

As for your maintenance, stop using the maintenance plan for indexes and stats and start using Ola's solution instead http://ola.hallengren.com/

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

suedunham2 (11/24/2014)
Thank you for that prompt reply - it looks like there is no way of reducing the size of the first Monday tran log dump unless I change the database from full to simple recovery mode for the duration of the maint plan. Is that something you would recommend?
Thanks also for the helpful PS! I did not set up the maintenance plan and can see that whoever did has not made good choices. Is it right that if I set it to rebuild that would be enough (because it renders reorganise and upd stats redundant)? The largest database that we are managing is Sharepoint, I have heard that the tables are prone to fragmentation - is rebuild the best choice here too?

To emphasize what Gail has already said, don't change the recovery model. Instead, start doing transaction log backups on the weekends just like during the week.

Also, the maintenance plan rebuilds rebuild everything and it's all logged. That will make your log files much larger than necessary which will also make a restore take much longer if it ever comes to that. Use Ola's script for that as Gail suggested. It will figure out if an index needs to be rebuilt, reorganized, or just left alone. It'll do the same with statistics.

Just in case you don't know, make sure that you're backups aren't going to the same disks as the data because if the data and the backups live on the same drives and something really bad happens to the disk system, you won't have the data or the backups to get back in business quickly.

Make sure that someone is taking tape backups on the disk backups. 😉

You also need to make a recovery plan which includes backups. If you have the space for it (and disk space is relatively cheap so you should get the space for it if it's not already available), take a full backup every night and log file backups every 15 to 30 minutes, certainly no longer than once an hour. Then, practice doing a restore to a test box at least once a month. You'll thank your lucky stars that you know how to quickly and easily do such a thing if something ever goes wrong and you need to do a restore for real.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

And make sure you do some test restores to ensure you can get your database back and that you know how to use the log backups that you're taking.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

suedunham2 SSC Rookie Points: 30 More actions · Answer 6

Thanks all for the helpful advice, really useful. You are stars!