Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, size, etc.).

Is there anything I need to do (or be cautious of) with the replication setup before trying to perform any maintenance on the log file? I am not an expert in the area of replication and cannot find anything solid that provides guidance as to what measures should be taken.

Edit: This would include working on the distribution database as well, data retention was not configured at all for some reason.

share|improve this question

1 Answer 1

I would first concentrate on the Autogrowth settings for the database as too frequent and incorrect autogrowths can cause massive internal log fragmentation affecting any processes that reads transaction log file (e.g. replication, CDC, backups, etc) and there-by soaring the number of VLF's. Remember that the log file has to grow as it cannot be ZERO initialized and hence if the autogrowth settings are not appropriate, then SQL Server has to wait till the auto growth event finishes.

Now, if you are using T-Rep, the log reader agent will read the log when replicating changes from the publisher to distributor and then the distributor agent will distribute the changes to the subscribers which are called "pending transactions". Once the changes are replicated, the log entry is marked as "replicated".

The problem arises when the log reader becomes slow or it gets delayed leading to accumulating "pending replication" commands for long periods and they will remain part of active log and hence VLF cannot be truncated.

You can check the log_reuse_wait_desc column in sys.databases which will be showing "Replication" as the problem.

Note: Merge replication and snapshot replication do not affect transaction log size. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database.

If the log file grows out of control, a common technique for T-Rep is to use sp_repldone command to mark all the log records currently waiting on the log reader as replicated and then you have to re-initialize the subscribers.

Note: Even if you try to switch to simple recovery model thinking that it will truncate the log, it wont work as replication is supported in SIMPLE recover as well, and the log wont be able to get truncated as the log reader agent will need them to process.

This brings us to doing Proper Log Management - size the log file to support the volume of data changes occuring in the database and take log backups frequently so as to allow rapid reuse of space within the log file.

Best is to monitor your Transaction log along with other monitoring that you might be normally doing using PERFMON and DMV's.

Just and FYI, sys.dm_db_log_space_usage is a new DMV in SQL Server 2012 that will get basic log size and space info.

References :

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.