Problem:
transaction log size of WSSContentMOSS increase continuing, even after backup transaction log still cannot shrink the log file.
Research:
1. Execute ‘DBCC loginfo’ found there are a lot of entries with status=2 that means the log is used.
2. Select * from sys.databases – log_reuse_wait_desc=’REPLICATION’
3. DBCC opentran –
Transaction information for database ‘WSSContentMOSS’.
Replicated Transaction Information:
Oldest distributed LSN: (0.0.0)
Oldest non-distributed LSN: (92353:9934:1)
4. Did not setup replication on this database
5. Check if there is a mirroring on the database
SELECT
A.name,
CASE
WHEN B.mirroring_state is NULL THEN ‘Mirroring not configured’
ELSE ‘Mirroring configured’
END as MirroringState
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4
ORDER BY A.NAME
Resolution:
1. Use [db_name]
Sp_removedbreplication
2. Check log_reuse_wait_desc again, it changed to ‘NOTHING’
3. Backup transaction log and shrink it, the size reduced.
