sql server 2008 R2 , sp1, 64 bit enterprise edition.

My database is in simple recovery model.

It is a publisher.

While running reorg on a big table , transaction log grows big . 

When I checked sysdatabases during this time log_reuse_wait_desc  is in 'Replicate' ..  VLS's are almost 1000 , they are in status 2 , so not able to shrink. It takes almost 1-2 hrs for replication to catch up and then VLS's reduce.

Initial size of Log file is 2 GB and autogrowth with 1024 MB .

Please advise on how I can tackle this problem.

Hi,

your database is part of replication. the below mentioned workaround might help,

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/34ab68ad-706d-43c4-8def-38c09e3bfc3b/logreusewaitdesc-replication-transaction-log-wont-stop-g

Yes it is

This work around will not work. We have transactional replication and we are having this issue during Reorg process.

Hi,

Did you checked the fragementation of the indexes that you are doing Reorg? For large, heavily fragmented indexes the reorganization operation is much less efficient than the index rebuild operation.

We are doing rebuild and reorg based on fragmentation %. between 10 and 30 reorg , above 30 rebuild.

As mentioned, perhaps reorg isn't optimal for you because of the replication situation! Consider doing rebuilds instead. I think that will be performed as minimally logged operations even with replication in place (but I'm not certain, so test/verify).

Agree with what Tibor said..

Rebuild can use minimally logging to reduce transaction log growth but Reorganize is always fully logged and also doesn't prevent transaction log clearing..more details in 

Technet QA by Paul Randal