Databse mirroring - large log files

Question

Post reply

Databse mirroring - large log files

Viewing 4 posts - 1 through 5 (of 5 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Check the log_reuse_wait_desc in sys.databases

what it returns

I suggest you to check Robert L. Davis's blog it'll give you good picture

http://www.sqlsoldier.com/wp/sqlserver/databasemirroringperformancecounters

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

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

Whack the index reorg from the plan and rerun again tonight (or earliest convinience).

Then put this in place (daily if possible).

http://sqlfool.com/2010/04/index-defrag-script-v4-0

archangel717 Grasshopper Points: 10 More actions · Answer 3

Thanks for the suggestions!

For the intense index defrag script... will this solve the massive amounts of data being written to T-LOG during my maintenance plans? I'm inferring that you mean that running them once per week is not enough for a high traffic db, and that to decrease speed/transactions to run them every night? I guess I never really noticed that index defrag/update stats/etc. were writing that much to logs.

I will give the script a shot tonight and see how it goes.

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

archangel717 (6/27/2011)
Thanks for the suggestions!
For the intense index defrag script... will this solve the massive amounts of data being written to T-LOG during my maintenance plans? I'm inferring that you mean that running them once per week is not enough for a high traffic db, and that to decrease speed/transactions to run them every night? I guess I never really noticed that index defrag/update stats/etc. were writing that much to logs.
I will give the script a shot tonight and see how it goes.

2 things. If you do this daily then you'll have less work (each run) to do than if you do it only weekly.

I've also noticed (tho not proven) that a straight rebuild used less ressources, including logs than reorg did. I'll need to prove this statement in your env. but as a test on a test server I'd give the script a whirl once with reorg at 10% and rebuild at 30% and then another go with rebuilds at 10% and see what takes longer and what takes more space in the logs for you systems.