Recovery Model of Subscription Database

Hi:

I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.

Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.

Thanks

No they don't, simple will be fine, but what's the problem you are having?|||

Thanks for replying. Log of the subscription database is about 96% full. I tired to truncate it but still size stays around the same. Even though I changed the recovery model to simple, it should truncate it at every checkpoint. The size is staying about the same.

There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.

Thanks

|||

Mike Husar wrote:

There are some active transactions sitting in the log. By truncating it multiple times, it should remove (purge) them. Actually log agent was stopped manually. Probably these active transactions may be partial and do not how to commit. Any ideas will be helpful.

You mention logreader agent, which should only affect published database. Are you talking about published or subscriber databases? If published database, only transactions that are marked as replicated (txns that the log reader agent has already replicated) can be truncated. Non-replicated txns will not be truncated.

|||

Actually it is not the logreader agent. It is the log of subcription database that I was not able to truncate.

|||Then you need to find out what's blocking. Usually this is due to active backup or restore, an active transaction, or some kind of diagnostic log scan.|||You can also try restarting the server to clear those active/open transactions, but then the uncommitted transactions will get rolled back.sql