Transaction log backups filling up much of the space --please help

Question

Transaction log backups filling up much of the space --please help

pinky

SSCommitted

Points: 1997
More actions
January 19, 2007 at 9:32 am

#153392

Hi!!!
Can anyone suggest me a good solution for this?
Currently we are taking a full backup every night and a differential backup every 4hrs. But now we have decided to take a full backup every night and transaction log backups every 30 min. Here comes the issue, we have some Reindex job that runs at around 3.00 PM every night which would run for almost 4hrs. At that time the transaction log file is filling up much of the space till the job completes.
Please suggest me a better way to schedule these backups. Thanks for all your help.

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

The REINDEX will fill up the tlog. It has to. REINDEX is all or nothing, so if it fails it needs the tlog to rollback everything.

Okay, you have a REINDEX job that goes from 3 PM to 7 PM. Schedule your fullbackup for after the reindex job. Then schedule your tlog backups based on that.

How do you currently schedule your REINDEX and BACKUP? Are they maintenance plans or just jobs that you scripted? If the later, make it two jobs. First job, first step will the REINDEXING, then the second step is the FullBackup. The second job should have one step, the transaction log backup. Schedule it to begin running half an hour after the first job finishes (you will have to 'guesstimate' how long the first job takes).

-SQLBill

pinky SSCommitted Points: 1997 More actions · Answer 2

Thanks SQLBill for your solution!!!!

I have the Reindex job and the backup job which i scripted and the Tlog is through the maintenance plan. I agree with your suggestion. Here is my question.

I would run the Tlog backup till 2.30 AM

3 AM - around 7AM (or longer) my Reindex job runs

Take a full backup after the reindex

schedule the Tlog backup to run after the full backup.

so in this case i will not have any backups b/w 3AM - Full backup right? so if a problem occurs at around 6 AM i will not have a record of all the work that is done b/w 3 AM and 6AM. please correct me if i am wrong?

SQLBill SSC Guru Points: 51440 More actions · Answer 3

You are correct. And wrong.

Correct: You won't have a tlog backup saved off for that timeframe.

Wrong: One step that is usually overlooked when things go wrong is to IMMEDIATELY disable full/diff backups and do a tlog backup. If your full backup has not run, you can do a tlog backup and it will have everything from 230am until the time you run the backup. The only downfall is if something happens and the full backup runs before you catch it.

-SQLBill

Peter Yeoh Newbie Points: 9 More actions · Answer 4

A transaction log backup does not cause the database transaction logs to be 'cleared'. In the above scenario, a transaction log backup performed after the full backup will still contain transactions created between 2:30 A.M to the time of the backup.

'Pinky', if something does go wrong at 6 A.M, whatever committed transactions will be stored in the database data files. You will still 'have a record of all the work that is done b/w 3 AM and 6AM'.

Peter Yeoh
Developer
Red Gate Software

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 5

The question I would ask first:

Why are you running a huge REINDEX every night?

Unless this is set up so only part of the database gets hit every night, rotating through all the objects so individual objects are only reindexed every few weeks (or less frequently), you would have to put forth a compessling - and rare - argument to support this action.

Often the overuse of maintenance jobs is the result of misunderstanding of actual tuning. ("Things are slow... so defrag everything!") Make sure your stats are up to date, but the usual effect aggressive reindex/defrag jobs is just big tran log backups.

If your database was taking so much activity that you would need constant reindexing, then the tran log backups from your daily activities would be huge as well, a result of all that activity.

-Eddie

Eddie Wuerch
MCM: SQL

pinky SSCommitted Points: 1997 More actions · Answer 6

Thanks for the replies

Eddie - We are using a third party application and the Reindex stored proc is created by them. We usually do not create any tables or Stored proc's ourselves. so that Reindex job was set up to run every night as suggested by them. Since the transaction log backups are taking much of the space due to Reindexing that is done every night we are planning to break this Reindex job into small chunks, reindexing only few tables each night rather than doing all at a time. Please let me know your thoughts.

Thanks

MohammedU SSCoach Points: 19644 More actions · Answer 7

One more point...check the fragmentation before reindexing...if there is fragmentation then only reindex otherwise don't do it...

MohammedU
Microsoft SQL Server MVP

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 8

I'd just kill the job for a week and see if performance tanked, or other indicators that things were too fragged (i/o's per second steadily increase, i/o-related wait counts and times going up, DBCC fragmentation checks of the busiest tables, etc.) (Note: if you aren't taking regular performace reads for historical reporting, you'll just have to run some big queries from time to time to see if the logical reads go up)

I'm not saying this glibly, either. I've got one database with 3TB of live data, with over 200M row inserts/day. We killed all the optimization jobs (HUGE log backups there) and aren't seeing much of an effect from it. After I partition some of the huge tables, I'll do some spot defragging and reindexing from time to time, but fragmentation is incorrectly blamed for performance problems so often I sometimes feel MS should disable the defrag commands just to force people to look for the real problems. If fragmentation really is the big killer, then they need to get the indexes right the first time to prevent the kind of outrageous fragmentation necessary to require nightly defrags.

If your vendor insists that you you run full defrag/reindex/whatever jobs every night, then insist that you will be taking the cost of additional disk and tape required to handle the backups out of your support contract payments. They can have the money back when they actually support the product and fix the performance problems for real.

-Eddie

Eddie Wuerch
MCM: SQL