Post reply

Shrinking the MSDB logs

  • October 1, 2010 at 1:29 pm

    #143626

    the MSDB log is about 15 GB whereas the data file is only 6 GB. Is there a way to shrink the MSDB log?

    Thanks

  • October 1, 2010 at 1:43 pm

    #1230482

    Is your MSDB on Full recovery or something? I've never seen an MSDB log that large.

    Also run dbcc opentran on it to see if there's an open transaction causing it not to be cleared out.

  • October 1, 2010 at 1:52 pm

    #1230489

    the recovery mode is set to simple (the deault). Can I change that to Full in MSDB with no issues?

    Thanks

  • October 1, 2010 at 2:12 pm

    #1230506

    There's really no reason to set MSDB to full. I'm not even sure if you can, actually. Anyway I was just verifying to make sure it was set to simple.

    Run DBCC Opentran on it to see if there's some kind of long running process that's filling the log for some reason.

    If not, run dbcc sqlperf(Logspace) and see how much of the log is actually in use.

  • October 1, 2010 at 2:19 pm

    #1230510

    Hi,

    By default, msdb uses the simple recovery model. If you use the backup and restore history information in msdb when you recover user databases, it is recommend that you use the full recovery model for msdb and that you consider placing the msdb transaction log on fault tolerant storage.

    After Switching from the Simple Recovery Model

    If you must switch from the simple recovery model to the full recovery model, it is recommend that you:

    Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

    The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

    Schedule regular log backups and update your restore plan accordingly.

    Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.

    Thanks

    Parthi

    Thanks
    Parthi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply