September 27, 2015 at 4:11 am
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 400GB+. The data (.mdf) file is only 3GB.
• Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..." help
• I've already deleted much of the history
• The MSDB database is in "Simple" recovery mode
• I've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
Please help me shrink the recalcitrant log file on this SQL 2008R2 database.
September 27, 2015 at 4:24 am
Longshot: CDC enabled on MSDB database?
September 27, 2015 at 4:29 am
No, but I have just discovered that the log reuse indicator for this database is "REPLICATION", and we're not replicating this MSDB database, so I'm trying to learn how to shut down whatever it thinks it's doing.
September 27, 2015 at 4:41 am
Glad you have found the cause. Maybe add some, daily, FileSize Logging so you can see growth-over-time 😉 Even 3GB sounds quite generous to me for MSDB MDB but, as with such things, "it depends".
Is housekeeping running on the Backup History in MSDB?
September 27, 2015 at 4:43 am
Just in case useful:
-- Oldest backup record in MSDB
SELECTMIN(backup_start_date)
FROMmsdb.dbo.backupset
October 13, 2015 at 10:38 am
I was ultimately successful by calling procedure sp_removereplication, even though the database was not actually doing any replication.
Something was just corrupted.
https://technet.microsoft.com/en-us/library/ms188734%28v=sql.105%29.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply