Large Transaction Log Backup after running DB Maintenance

Question

Large Transaction Log Backup after running DB Maintenance

Lisa Mears

Grasshopper

Points: 12
More actions
April 8, 2005 at 10:20 am

#110365

I have a scheduled database Integrity check Maintenance job that is run weekly as well as a job that performs reoranization on data and indexes. The first transaction log backup that is run after these Maintenace jobs are run is extremely large. Is this normal?
Thanks in advance.

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Unfortunately, based on your information I would say YES. The 1st T-LOG after your re-index would be VERY large (assumption is CLUSTERED INDEX re-orgs).

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Jp Default port Points: 1419 More actions · Answer 2

I also had the same issue with one of db (~200 GB).It was genereating almost 50-60 gb tlog backup files.We work around with :

-not using checkdb or reindex jobs created using maint plans.

-setup another job which run a proc that does reindex on each table.By doing this tlog backup job would be able to create smaller backup files.Now we are getting almost gig files as oppose to 50-60 gig.

hth.

jp.

Lisa Mears Grasshopper Points: 12 More actions · Answer 3

Thanks for the repsonse. So then it sounds like if I create a separarte job, outside of the maintenance plan, to reindex each table, the T-Log dumps are smaller. Would you suggest that I use the DBCC DBREINDEX command in this separate procedure to accomplish this?

Thanks,

Lisa

Jp Default port Points: 1419 More actions · Answer 4

yes . Use dbcc dbreindex for each table .It should work in most cases.Remember tlog backup job should run frequently (e.g.every 5 mins) to have small backup files.If it don't run at all,you will be end up with the same problem again.

While dbcc is moving to next table ,the tlog backup job gets a chance to complete itself and hence creating smaller backup files.

-jp.

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 5

One more suggestion. You could set your db into SIMPLE RECOVERY model before running reindexing. Thus, you can avoid large tr. log backups.

1.Perform last transaction log backup to capture all changes prior to switch.

2.switch recovery to simple

3.do maintenance

4.switch back to full

5.perform full backup and start performing tr.log backups again.

The drawback is that you won't be able to recover your db to the time when it's in SIMPLE model. But if it's not 24/7 application, it might not pose any problems because maintenance is probably run during non-working hours.