transaction log almost full

Question

transaction log almost full

sqlfriend

SSC Guru

Points: 52794
More actions
July 23, 2011 at 12:54 pm

#244792

We have a maintence plan full sunday, daily differietial and hours transaction backup.
At weekend we also have a reindex maintence job, and one hour right after it there is a transaction backup. It usually failed is because no space.
I know after the reindex job, the transaction log usuallly becomes big. The transaction log backup size is almost the same as the full backup.
We really don't need to log the reindex process.
Is there a way I can avoid the transaction log backup to avoid no space issue?

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

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

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 1

one option could be to switch to Ola Hallengrens more intelligent rebuild solution script at http://ola.hallengren.com/ in stead of a classic maintenance plan. (there are also alike scripts at SSC, but I don't have the refs at hand)

This way only objects with a given fragmentation will get rebuilt.

Another option may be to switch your db to bulk logged recovery model during maintenance windows.

ref: BOL topic Operations That Can Be Minimally Logged

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

sqlfriends (7/23/2011)
We really don't need to log the reindex process.

Yes you do, because if it wasn't logged and a rebuild failed for any reason (out of space, cancelled, server restart) your database would have to be marked suspect and unusable.

Is there a way I can avoid the transaction log backup to avoid no space issue?

You can use bulk-logged recovery. That way the log won't get as full, but the log backup will be the same size. Otherwise rebuild just what needs rebuilding and not everything.

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Thanks.

When you say bulk-logged recovery, I guess during the weekend maintence job, I can first change from full to bulk-logged recovery, then do the reindex, then change it back to full. My question is right here, does this change of recovery mode break the backup chain, I mean can I still use the full backup or differential backup before the change of recovery mode?

Another question if database is marked suspect, then what should I do?

I will also check into the scripst about only reindex those needed.

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

sqlfriends (7/24/2011)
When you say bulk-logged recovery, I guess during the weekend maintence job, I can first change from full to bulk-logged recovery, then do the reindex, then change it back to full. My question is right here, does this change of recovery mode break the backup chain, I mean can I still use the full backup or differential backup before the change of recovery mode?

It does not break the backup chain. That's why it's recommended over simple recovery for stuff like this.

You should take a log backup right after switching back to full recovery.

Another question if database is marked suspect, then what should I do?

Restore from backup.

That's why I'm saying that everything absolutely does need to be logged, regardless of what you think. The log is for database integrity and durability primarily. (there's no way to do any database change without logging it, as it should be)

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

sqlfriend

SSC Guru

Points: 52794

July 24, 2011 at 12:08 pm

#1357555

Thanks a lot.

Steve Jones - SSC Editor SSC Guru Points: 738803 More actions · Answer 6

I would recommend you follow Gail's advice, and also that you switch to more intelligent rebuilding, but if you are running out of space on the disks, you need to start planning for more space. Start the ordering process now. At some point your data size will grow and then you'll be stuck, even with more intelligent maintenance.