Index Reorg Causing Transaction log to fill up

Question

Index Reorg Causing Transaction log to fill up

Patti Johnson

SSCertifiable

Points: 7054
More actions
September 24, 2011 at 10:03 am

#245403

I am trying to run an index reorg on a SQL 2008 database. We have full recovery set on this DB and transaction log backups run every 15 mins. I also checked and the transaction log size is set to restricted but at 2TB. Based on that I really have unlimited growth for the T-Log. I also have database mirroring setup for this DB. How can I get an index reorg to run if the T-Log fills up? I read in many blogs, and posts that changing the Recovery Model to Bulk Log would enable the index reorg to run and then change it back to Full after the index reorg is done. Is that a best practice and can I change the recovery model to bulk with database mirroring enabled?
Thank you
Patti

Viewing 14 posts - 1 through 13 (of 13 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

If you are filling up a 2TB t-log, I'd split the reorg up into a targeted approach. Try doing a handful of tables per night and rotate through a schedule.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 2

Thank you. Yes, I guess that is an option. I remember seeing a script somewhere that identifies those indexes that meet the requirement for an index reorg and only selects those. I'm looking around. Do you know what script I am referring to?

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

Bulk logged recovery won't help. Index reorg is a full-logged operation, always. (it's rebuild that is minimally logged in bulk-logged and simple). Besides, mirroring requires full recovery.

You can increase the frequency of log backups during the reorg. Reorg is done as lots of small transactions, so the log can clear if a log backup runs

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

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 4

Thanks Gail. I didn't realize that. I thought I read that an index reorg is considered one long transaction. I must be getting my index rebuild and reorgs mixed up. Yes, then increasing the frequency of the transaction log backups during an index reorg is the best solution.

Thanks you again.

Patti

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

Patti Johnson (9/24/2011)
I thought I read that an index reorg is considered one long transaction.

Rebuilds are a single transaction, fully logged in full recovery, minimally logged in bulk-logged and simple. Index reorg is done as lots of little transactions and is fully logged in all recovery models.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Patti Johnson (9/24/2011)
Thank you. Yes, I guess that is an option. I remember seeing a script somewhere that identifies those indexes that meet the requirement for an index reorg and only selects those. I'm looking around. Do you know what script I am referring to?

This is probably the one you were looking for.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 7

Yes, this is a good one. I was thinking about a script that based on the fragmentation of the index would pick if it should reorg it or rebuild it. I don't think this script does that. However, I can use this one. Thanks for looking.

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

Patti Johnson (9/25/2011)
Yes, this is a good one. I was thinking about a script that based on the fragmentation of the index would pick if it should reorg it or rebuild it. I don't think this script does that. However, I can use this one. Thanks for looking.

That's exactly what it does and the 1 I have in prod right now.

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 9

Good morning all. I have a question. If I run an Alter Index Command as follows with the database in Bulk-logged model and the Alter Index fails, will the table MyTable and all its indexes 'Roll Back' to their original state as would happen under Full logging model?

ALTER INDEX ALL ON MyTable REBUILD WITH (ONLINE=OFF, FILLFACTOR = 80)

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

Recovery model does not EVER affect the ability to roll a transaction back.

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

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 11

Thanks. This is what my testing showed, just wanted to make double sure about the roll back. Data loss in not acceptable...

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

Let's put it this way, if a operation fails or is cancelled and rollback cannot be completed, the database must be marked suspect immediately.

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

markenash Mr or Mrs. 500 Points: 507 More actions · Answer 13

I just wanted confirmation that a Rollback would occur on the failed Alter Database Rebuild command I presented, in Bulk-Logged model. If the rollback fails for whatever reason, then a SQL Backup restore is probably the next step.

thanks again..