SQL MSDB growing unchecked

Posted on 2011-05-11
Last Modified: 2012-05-11
I have changed the MSDB databse on a 2005 SQl server machine to Simple Recovery Model. Yet, the MSDBLog is still growing to over 5 GB every night. Can anybody tell me what might be going on?

Thanks in advance
Question by:raybar111
    LVL 8

    Expert Comment

    Yes, you need to take the database offline before the Simple Recovery Model will implement. Once you have taken it offline and then bring it back online the Log file will either self delete or you can delete it your self and the simple logging will start to take place.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Yet, the MSDBLog is still growing to over 5 GB every night. Can anybody tell me what might be going on?
    at least one of the jobs in msdb are using that much transaction space.
    the issue is to find out which one it is, changing to simple recovery mode alone will not help if it's a single transaction.

    note that if you don't "shrink" the msdblog, it will normally stay at that size, and not grow by 5GB every night, but reuse the space.
    LVL 8

    Accepted Solution

    You can download Apex SQL Log
    or some other log reader to view the log file and see what transaction is causing the increase in size. These log readers are usually not free thought.  Or you can try to read teh log file raw using similar syntax to this replacing for your database of course:

    SELECT * FROM master.dbo.fn_dblog(null, null)
    LVL 8

    Expert Comment

    In most cases the syntax above will work.

    Author Comment

    This is the MSDB database and it is telling me that the OFFLINE option cant be set for MSDB.
    LVL 8

    Assisted Solution

    hum... try rebooting or stopping SQL services.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now