June 26, 2011 at 1:59 pm
All,
I've just implemented a new SQL 2008 R2 implementation with 2 identical boxes mirroring several databases. No witness server in synchronous mode.
I've only had this system in production for 3 days. Well I noticed something very strange after I configured the maintenance plans, and now I've got a 45GB log file on the 3rd day. This is a high traffic db (around 30GB size), but not a lot of traffic over this weekend and still a huge log. Without mirroring, the log has never gotten this high, so I know something is up with the mirror or something else.
I am running full backups every 3 hours and T-LOG backups every 15 minutes, so I'm not sure why the log is even growing that big. The databases are always saying synchronized, so it doesn't look like an issue where the transactions are not getting committed to the mirror. In my investigation, I found something really odd. On Sunday early morning, I run a couple of tasks that checks dbs, reorganizes indexes, rebuilds stats and cleans up old maintenance plan history. These only run on Sunday. I've attached an image of the log files backups during that period and they are huge. Is this normal? I'm thinking this is the only reason they grew so big in a short period, but I can't imagine this being normal.
The seconds part is, how can I shrink the log file itself now? It's truncating the logs correctly, because its 99% free space in the file, but the file is too big. When I run dbcc shrinkfile, it nevers does it, as I guess it's waiting to commit to mirror? Any help here would be great.
Thanks!
June 27, 2011 at 6:16 am
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/
June 27, 2011 at 6:45 am
Whack the index reorg from the plan and rerun again tonight (or earliest convinience).
Then put this in place (daily if possible).
June 27, 2011 at 12:37 pm
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.
June 27, 2011 at 2:52 pm
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply