MS SQL system database MSDB log file is huge

The server is SQL 2005 sp2, and it’s acting as a main server for one application. Everything is working fine, and the onliest problem I have is that MSDBLog file is growing.
The MSDB dB is currently about 100M and log file is more than 1T.
MSDB is in simple recovery mode (by default) and I have daily backups.

I was unable to shrink/truncate log file, so I try to find where is the problem and i was so strange.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'msdb'

Should return NOTHING for log_reuse_wait_desc – in my case log was waiting for replication. I changed it with

EXEC sp_removedbreplication msdb

And finally I get the chance to shrink my log file.





Leave a Reply

Your email address will not be published. Required fields are marked *