Index maintenance

Category: sql server mirroring

Question

VijayKSQL on Thu, 10 Apr 2014 07:15:10


How to create index maintenance jobs in DB mirroring, as it doesn't support bulk-recovery?

Replies

Shanky_621 on Thu, 10 Apr 2014 07:51:18


How to create index maintenance jobs in DB mirroring, as it doesn't support bulk-recovery?


Index rebuild job creation would be same.You create a script to find out fragmented indexes and then you rebuild it.Index rebuild will be minimally logged in bulk logged operation but there wont be much effect on log file size just logging will be minimal.

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


VijayKSQL on Thu, 10 Apr 2014 16:21:25


I think database mirroring doesn't support Bulkrecovery model.

Normally in logshipping before running index rebuild/reorganize we put into bulklogged then after completioin of index rebuild we put them back to full recovery.

Prashanth Jayaram on Thu, 10 Apr 2014 16:35:07


Yes, you are right. Database mirroring doesn't support bulk recovery model.

Perform index maintenance task based on threshold values and the object which are most eligible for rebuild. It will create more traffic. 

Refer the below link

http://technet.microsoft.com/en-us/library/cc917681.aspx

-Prashanth