In Why Does the Transaction Log Keep Growing or Run Out of Space?, two common reasons are outlined for why a transaction log grows:
- The database is in full recovery mode, and log backups are not being taken.
- 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.
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.
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)?
sp_removedbreplication(those two steps were recommended in some thread I can't find now, in the event that deleting the publication doesn't work, which it didn't).
is_cdc_enabledis set to 0 for all databases. I'm going to read through Scott's and your links, and try out your suggestion. Might take me a bit.