0

I have a mirrored database in full safety mode on sql server 2008 R2 Standard Edition. I want to create a maintenance plan to rebuild and reorganize the indexes on a weekly basis. Maintenance plans are set to take full backups 3 times a day and transaction log backups every 30 minutes. The database is very active and gets lot of traffic. I tried rebuilding the indexes manually during non peak hours and that caused the log file to grow heavily on the principle as well as on the mirror.

Are there any specific steps to be followed for rebuilding indexes in a mirroring environment? I searched online but could not find any difference in the way indexes are rebuilt in mirroring and without mirroring.

Also can I just use DBCC SHRINKFILE on the principle to free the unused space in the log file without stopping the mirroring?

kishore
  • 872
  • 3
  • 13
  • 28
  • Rebuild OR reorganize, not both. Also is full safety mode a function of mirroring or are you referring to Full Recovery Model? – joeqwerty Jan 28 '11 at 00:26
  • Sorry, I don't rebuild and reorganize at the same time. I want to create one maintenance plan to reorganize the indexes and use it more frequently and a different plan to rebuild indexes( To be used less frequently). Full safety mode indicates the existence of a witness server and also all the transactions will be committed synchronously. – kishore Jan 28 '11 at 00:34

2 Answers2

3

I would strongly discourage you from regularly shrinking the log in a mirrored situation. One day your log will grow on the primary and not have disk space to grow on mirror, primary will not be able to ship log and will be pin the log in place until it grows ad-nauseam and you'll be cursing the job that shrinked your log as you won't be able to resume the mirroring without some complicated hockey-pockey.

If the log grows, it means it needs the extra space. Eventually it will stabilize at the right size and stop growing. That size, whatever will be, is the the operational size required for your log. If anything, you should grow your log right now so it stops growing automatically.

As for the index maintenance, do you have evidence that they need rebuilding at the frequency you're proposing? There is no way to avoid reduce the log space required during index rebuild operations if the database is mirrored.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
0

Yes - there is a big difference in rebuilding indexes on non-mirrored db and mirrored db's. The impact is higher and some things must be checked out before doing this.

Most important you should change from full-safety to high-performance for the maintenance period so that it will not wait the mirror while re-organizing the index.

The log file can be shrinked if you really need to do this but it will not take effect if there are active transactions in it not backed-up. The shrink can be done on a mirrored database in theory, but don't do it on data files because the fragmentation on indexes will grow very much.

sparrowt
  • 257
  • 2
  • 7
yrushka
  • 600
  • 2
  • 9
  • Thanks for sharing the post. I am using Sql Server 2008 R2 Standard edition that does not allow mirroring in high performance mode. Taking a transaction log backup and shrinking the log file immediately should help in shrinking log file on principle as well as mirror right? – kishore Jan 28 '11 at 15:45
  • 1
    Yes it normally should. Every change on data and log files are mirrored on databases from Mirror server. As a workaround, you could suspend the mirroring session between two databases, perform re-index and then resume it. http://msdn.microsoft.com/en-us/library/ms175059.aspx – yrushka Jan 28 '11 at 17:16
  • Don't shrink the database log. The database is going to just grow the log again the next time you re-index. If space is that short, buy more hard drives. – mrdenny Feb 10 '11 at 00:51
  • @mrdenny. needless to say what "buy more hard drives" means for some managers. In emergency situation this can be done without affecting database performance. The best approach though is to plan a good recovery model and taking regular backup logs. In mirroring you absolutely need log backups because databases have to be in Full recovery model -> The shrink will not be necessary and your business will be a lot safer. – yrushka Feb 10 '11 at 08:26