March 21, 2012 at 11:50 pm
Hi
I have a sql server 2008 database which has a marker of "Replication" in the Log_reuse_asit_desc column under sysdatabases. I need to remove this replication as the transactions on the database log files are not being closed thus increasing the size of the log causing the database to fall over. (Transactional backups are not an issue!) This is what i have tried thus far:
1. executed: "exec sp_removedbreplication commands"
2. use sp_dboption and set database to publish false via given params.
3. Setup an new replciation set on existing db and removed the replcaiton via syntax also try to manually remove via SSMS studio (right click delete).
NB: In order for me to close replicated transactions i need to use the sp_Repldone stored proc.
I am also system administration on the server so its not a perms issue.
Anyone out than have any more suggestions?
March 22, 2012 at 5:47 am
I am curious. How do you know the transactions are not being closed?
Are you actually making transaction log backups on the database?
March 22, 2012 at 6:51 am
Hi
The Db is currently in the full recovery model and transaction log backukps are run every 30min. The problem is that the log is not being cleared after the log backup has completed thus indicating uncommitted transactions.(99% chance due to the DB replication).
Ta
March 22, 2012 at 7:02 am
Have you checked this?
BOL
Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. 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 transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs or setting it run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running. For more information about how to check Log Reader Agent status, see How to: View Information and Perform Tasks for the Agents Associated With a Publication (Replication Monitor).Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups. For more information about how to back up and restore databases involved in transactional replication, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.
March 22, 2012 at 7:26 am
WOW, i can't believe i have over looked that! I have deleted all replication jobs. Now its just a matter of monitoring!
Thanks bud!!!
March 22, 2012 at 9:03 am
Glad I could help.
March 22, 2012 at 9:38 am
The transaction log will not be cleared on a replicated database until the commands that are meant to be replicated are hardened to the distribution database. This is done by the log reader agent.
It will run through the transaction log picking up commands and writing them to the distribution database. Once this is done the log reader will mark the VLF's as ready for reuse (assuming they are backed up in BULK/FULL recovery model).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply