October 18, 2010 at 7:54 pm
We are running 2005 Enterprise Edition in a Primary / Mirror / Witness configuration with Primary and Mirror servers at different data centers connected by VPN. Up until now, we have had no issues, but the size of database has recently grown quickly and our indexes were needing defragged. I ran the ALTER TABLE REORGANIZE command last night - it created a 10 G transaction log that had to be sent across sites. This has sucked up most of our bandwidth and is causing quite a few latency issues. A few questions:
1) Is there any way to mirror the data across sites, but not index information?
2) Does the ALTER TABLE REORGANIZE always create a similar size of data (assuming similar size in tables being defragged), or is dependent on how fragmented the data is? For example, if I were to run it again tonight, would I expect the transaction log to be 10 G, or should it be much smaller (again, last night was the first time this had ever been run on our database)?
3) I know 2008 supports compression for data synchronization - will this solve the problem?
Thanks,
Kevin
October 20, 2010 at 5:34 am
For the purposes of this post I'll assume you meant ALTER INDEX. I've just double checked and REORGANISE isnt a table option. 😉 REBUILD could work but I've never used it (I'll be testing it today!).
kcook-1066767 (10/18/2010)
1) Is there any way to mirror the data across sites, but not index information?
No. When you mirror databases everything is identical. You could look at replication which would distribute the commands and not the transaction log.
2) Does the ALTER TABLE REORGANIZE always create a similar size of data (assuming similar size in tables being defragged), or is dependent on how fragmented the data is? For example, if I were to run it again tonight, would I expect the transaction log to be 10 G, or should it be much smaller (again, last night was the first time this had ever been run on our database)?
You've almost answered your own. It is dependant on the fragmentation. The REORGANIZE option is a very intensive command when it comes to transaction logging so in this situation dropping the index and rebuilding it could be more effective.
3) I know 2008 supports compression for data synchronization - will this solve the problem?
Doubtful to make much of an impact but I dont know for sure without researching.
Hope this helps.
October 21, 2010 at 10:15 am
1) did you actually CHECK the fragmentation level before issuing the reorg command? reorg can actually log WAY more tlog data than the entire size of the table if fragmentation is severe enough.
2) Please consider hiring a professional to help you set up a good maintenance system and to transfer some knonwledge to you to prevent such things in the future.
3) ola.hallengren.com has some WONDERFUL, DOCUMENTED and FREE maintenance stuff at his site.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply