none
Rebuilding Indexes on Replicated databases

    Question

  • Hi Guys,
    What will be the effect on a database if it being published and I am going ti rebuild Indexes on it? Keeping in mind that database recovery mode is in full and it will log all the index maintenance transactions. It is on SQL server 2008 R2.

    Also I am building Indexes ONLINE.


    Kindly mark the reply as answer if they help


    • Changed type Sunil Gure Monday, August 27, 2012 8:50 AM
    • Edited by Sunil Gure Monday, August 27, 2012 10:24 AM
    Monday, August 27, 2012 8:50 AM

Answers

  • Thanks Hilary.
    I was expecting a reply from you on this. I think there is some more out of it. I cannot change the recovery mode to simple/bulk if database is also in database mirroring.

    But the issue which happened here the database was not in mirroring but it was in full recovery mode.

    Can you please tell me if there are any differences it will make in context of replication if we change the recovery mode to simple or bulk? It has to anyway log all the transaction which are marked for replication and hold them till they are moved to distribution.

    I ran ONLINE rebuild operation and it had created huge latency for Log reader. When I checked the number of such records which are marked for replication into log file it had retuned huge value (10 million):

    SELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'

    Also I have checked for number of VLF created using DBCC LOGINFO and found that 14K+ VLFs were created for the database.

    So I have to finally resolved the issue by shrinking the Tlog and hence reducing the number of VLF to 1K+.
    Also used a new profile for Log reader agent with high number of –ReadBatchSize  to 1000 and – MaxCmdsInTran 10000.

    I am still confused:  Does Index rebuild operation get logged? Why it not is logged in case of simple or bulk logged because transactional replication logs the transaction irrespective of recovery mode?

    Can you please suggest?

    I can see there is bit explanation about rebuilding ONLINE operation in context of log reader latency.

    http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx


    Kindly mark the reply as answer if they help

    • Marked as answer by Sunil Gure Tuesday, August 28, 2012 3:49 AM
    Monday, August 27, 2012 4:19 PM
  • Yes, changing the recovery model from full is not a good idea when mirroring.

    From what I understand indexing creates a lot of BCM entries in the log file. With simple recovery model the log is recycled when the active transactions are committed.

    With bulk logged certain operations like indexing is minimally logged.  

    This link has a good discussion on this:

    http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/764aae70-7bc4-4b3f-a2d0-f9510349888b


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Sunil Gure Tuesday, August 28, 2012 3:49 AM
    Monday, August 27, 2012 4:57 PM
    Moderator

All replies

  • There is no effect. I would change the recovery model to simple/bulk logged unless there is a reason to keep it as full (ie point in time recovery, log shipping).

    There were some issues IIRC with SQL 2005 with online index rebuilds skipping replication commands.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, August 27, 2012 3:48 PM
    Moderator
  • On a busy table you may find that creating the index will take a very long time - many times longer than doing an offline index rebuild

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, August 27, 2012 3:49 PM
    Moderator
  • Thanks Hilary.
    I was expecting a reply from you on this. I think there is some more out of it. I cannot change the recovery mode to simple/bulk if database is also in database mirroring.

    But the issue which happened here the database was not in mirroring but it was in full recovery mode.

    Can you please tell me if there are any differences it will make in context of replication if we change the recovery mode to simple or bulk? It has to anyway log all the transaction which are marked for replication and hold them till they are moved to distribution.

    I ran ONLINE rebuild operation and it had created huge latency for Log reader. When I checked the number of such records which are marked for replication into log file it had retuned huge value (10 million):

    SELECT count(*) FROM ::fn_dblog(NULL, NULL) WHERE Description='REPLICATE'

    Also I have checked for number of VLF created using DBCC LOGINFO and found that 14K+ VLFs were created for the database.

    So I have to finally resolved the issue by shrinking the Tlog and hence reducing the number of VLF to 1K+.
    Also used a new profile for Log reader agent with high number of –ReadBatchSize  to 1000 and – MaxCmdsInTran 10000.

    I am still confused:  Does Index rebuild operation get logged? Why it not is logged in case of simple or bulk logged because transactional replication logs the transaction irrespective of recovery mode?

    Can you please suggest?

    I can see there is bit explanation about rebuilding ONLINE operation in context of log reader latency.

    http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx


    Kindly mark the reply as answer if they help

    • Marked as answer by Sunil Gure Tuesday, August 28, 2012 3:49 AM
    Monday, August 27, 2012 4:19 PM
  • Yes, changing the recovery model from full is not a good idea when mirroring.

    From what I understand indexing creates a lot of BCM entries in the log file. With simple recovery model the log is recycled when the active transactions are committed.

    With bulk logged certain operations like indexing is minimally logged.  

    This link has a good discussion on this:

    http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/764aae70-7bc4-4b3f-a2d0-f9510349888b


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Sunil Gure Tuesday, August 28, 2012 3:49 AM
    Monday, August 27, 2012 4:57 PM
    Moderator