Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    32

    Unanswered: Shrinking the MSDB logs

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are you backing up the log for the msdb database?

  3. #3
    Join Date
    Jul 2002
    Posts
    32
    no I am not. The recovery model for that DB is set to simple , can you change that to Full?

    Thanks,

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run this in the msdb database. It will show which table(s) are taking up most of the space. a 6 GB msdb database is odd.
    Code:
    select object_name(p.object_id), p.index_id, f.name, sum(total_pages)/128, convert(varchar(10), getdate(), 101), count(*) as partitions
    from sys.partitions p join
        sys.allocation_units a on p.partition_id = a.container_id join
        sys.filegroups f on a.data_space_id = f.data_space_id
    group by p.object_id, p.index_id, f.name
    order by sum(total_pages)/128 desc
    To physically shrink the logfile, you can simply run DBCC SHRINKFILE (2, 1000), but I am unconvinced it will remain small for very long, as something has ballooned it to 16 GB, and I am not sure what that could be.

  5. #5
    Join Date
    Jul 2002
    Posts
    32
    but to be able to issue the DBCC SHRINKFILE (2, 1000), do I have to change the recovery model to full and get a couple of log backups?

    Thanks,

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not if it is in SIMPLE recovery mode. All that needs to be done is to make sure that the high Water Mark of the log file is low enough. If you don't get all the space back in the first run, run CHECKPOINT a few times to force the log to switch to a lower position in the file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •