SQL Server – Why Transaction Log File Grows Large After Disabling Replication

sql servertransaction-log

In Why Does the Transaction Log Keep Growing or Run Out of Space?, two common reasons are outlined for why a transaction log grows:

  1. The database is in full recovery mode, and log backups are not being
    taken.
  2. A long running transaction is taking up a lot of space.

As far as I can tell, neither of these reasons apply to me. My database is in simple recovery mode, and there are no long running transactions taking up a lot of space.

enter image description here

enter image description here

Note that I got lucky when I took the screenshot for DBCC OPENTRAN and there were no transactions active. Most of the time I see and see a transaction that started seconds ago.

Another answer suggests that my log might be large, but empty. To check this, I right clicked on the database, and navigated to Tasks -> Shrink -> Files, and selected the Log file type. I can see that my log file is large and also has very little free space.

enter image description here

I used Aaron Bertrand's query to figure out which sessions are causing the log file to grow and on what interval. The query tells me that the log file grows on a semi-regular interval, about once every 10 minutes. The SPID is different every time, and doesn't always come from the same application. I used a query (that I can't find now for some reason) to figure out what SQL statement that session was last executing. I studied those queries to see if anything stood out and didn't see anything obvious. I ultimately decided that if DBCC OPENTRAN said no transactions were running long, then the problem must not be in the queries being executed by the applications.

We recently attempted to set up transactional replication with this database as the source. We ended up having to abandon, but had issues removing the publication. We had to manually drop the distribution database and use sp_removedbreplication to clean up. I read that replication can cause the transaction log to grow unchecked. My thoughts are that since deleting the publication didn't go so smoothly, that maybe the log file is growing because some remnant of the replication stuck around.

So, aside from the two most common reasons, what else can I do to figure out why the transaction log is growing so large (and fix it)?

Best Answer

From your comment, you apparently had success re-enabling replication, and then removing it properly using the information contained in the post entitled Disable Publishing and Distribution.

To summarize the steps involved:

  1. Create a new publication with only one article (which we are about to delete anyway). When prompted for a schedule for the snapshot agent, choose any time that is not right now.
  2. Stop all replication-related jobs. For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.
  3. At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.
  4. At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.
  5. If the Publisher uses a remote Distributor, execute sp_dropdistributor.
  6. At the Distributor, execute sp_dropdistpublisher. This stored procedure should be run once for each Publisher registered at the Distributor.
  7. At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
  8. At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

NOTE:

If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.

Related Question