SQLServer log keeps growing

Recently our database log was growing bigger and bigger on our main development database. The DBA just blamed the developers because he meant they were not closing transactions correctly.

But DBCC OPENTRAN did not display any open transactions, but instead reported that SQLServer tried to replicate transactions, also we did not have any replication configured for this database.

DBCC OPENTRAN
Transaktionsinformationen für die ...-Datenbank.

Replizierte Transaktionsinformationen:
Älteste verteilte LSN : (0:0:0)
Älteste nicht verteilte LSN : (214984:95479:1)

Using fn_dblog I found the statement which belonged to the mentioned LSN (214984:95479:1 is in HEX 347C8:174F7:1)

select top 100 [Current LSN], [Begin Time], [Transaction Name], [AllocUnitName] from ::fn_dblog( null, null)

000347c8:000174f7:0001 2012/11/25 14:31:18:997 CheckDb NULL
000347c8:000174f7:0002 NULL NULL Unknown Alloc Unit
000347c8:000174f7:0003 NULL NULL ...

I found some information in the SQLServer Logs at this time that showed that CHECKDB did have some problems at this time, maybe it was even crashing, probably leaving the database in a problematic state.

Unfortunately I do not know how our DBA solved the problem, but at least he stopped blaming the developers ;-)

You will find some informations on the web about similar problems, search Google for sp_removedbreplication or sp_repldone.

This query may also help to find out why log space is not reused:

select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

Also check out these links:

• http://serverfault.com/questions/280547/sql-server-log-file-wont-shrink-due-cause-log-are-pending-replication-on-non
• http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/adc5a430-25e6-4eba-93e2-98a5d952dcf4