The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Sql server 2008 R2

Trying to shrink the logfile for out Team Foundation 2010 database:



BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'


DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

I get the followin error message:

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

Trying the solution with sp_repldone:

USE Tfs_DefaultCollection

GO

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

exec sp_replicationdboption @dbname = N'Tfs_DefaultCollection', @optname = N'merge publish', @value = N'true'

GO

Msg 20028, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 60

The Distributor has not been installed correctly. Could not enable database for publishing.

The replication option 'merge publish' of database 'Tfs_DefaultCollection' has been set to false.

Running opentran show we have some unreplicated data, that's really strange because there is no replication setup for this database:

dbcc opentran

Transaction information for database 'Tfs_DefaultCollection'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (193362:85316:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sp_removedbreplication Tfs_DefaultCollection

Msg 1205, Level 13, State 57, Procedure sp_MSrepl_clean_replication_bit, Line 25

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

alter database Tfs_DefaultCollection set offline with rollback immediate

Msg 1205, Level 13, State 68, Line 1

Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

I founbd and killed one of my own sessions...

alter database Tfs_DefaultCollection set offline with rollback immediate

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Failed to restart the current database. The current database is switched to master.

sp_removedbreplication Tfs_DefaultCollection

alter database Tfs_DefaultCollection set online with rollback immediate

Now running opentran again:

dbcc opentran

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BACKUP LOG  Tfs_DefaultCollection TO DISK='NUL:'

DBCC SHRINKFILE(Tfs_DefaultCollection_log, 1)

Success!