The Mystery Of The Model Database Transaction Log Growth

I had to play detective a bit this week. I get a weekly report that gives me a list of databases that have more than 50 VLFs in their transaction log files. I posted the code that generates it here. My transaction logs are in pretty good shape now and I only occasionally get a database with a large number of VLFs. But last week, I got a report that had me baffled. On one of my servers, the model database had 58 VLFs. Why would the model database logfile be growing? I checked my database filesize report and saw the following:

Click to enbiggen

Hmm. Nothing should be using the model db. It’s an empty database used as a template when a new database is created. I looked in the database, think maybe something, somewhere was using it. It contained no user objects. Then I checked my jobs, thinking maybe I had a job running in that database by mistake. Nope. In a final effort to track down what was going on, I started a trace to capture all activity in the database. I left it running for a week. The only activity I saw was my backup jobs.

I posted to Twitter about it and the only responses were “something is using the database.” Except nothing was and I had the trace to prove it. Today, I came across a post on SQLServerCentral.com from someone else who had the same issue. The consensus seemed to be that if the model database is in full recovery mode (and mine was), then backups are logged event and could cause the transaction log to grow if the transaction log was not being backup up (mine wasn’t). Gail Shaw stated it would take years to fill up a transaction log from just backup entries. She didn’t show how she calculated that number and I agree, it would take a long time. However, later on in the thread, a link was posted to an entry from the Microsoft CSS SQL Server Engineer’s blog that mentioned this exact same issue. They confirmed backups of model can cause the transaction log to grow in certain situations.

It seems this was the cause in my situation. In fact, I checked all my servers and saw several instances where the model transaction log had grown. There were also cases where it hadn’t grown, so I fully believe this is something that takes a long time to manifest itself. My solution was to include the model database in my transaction log backup jobs, which has stopped this from happening. Another alternative would be to change the model database to simple recovery mode, but I didn’t want to do that because I want the default for new databases to be full recovery mode.

Share

12 thoughts on “The Mystery Of The Model Database Transaction Log Growth

  1. I ran backups for a new database continually in a loop until the log reached the size the person was reporting, then counted how many backups had been made. Nothing fancy.

  2. Shawn,
    I am interested in the reports that you created in above image, could you share how you did it, any code about it?

    thanks

  3. That report was made using SSRS. I wrote about the method I used to collect the data in an article on sqlservercentral.com, later updated at http://shaunjstuart.com/archive/2010/11/drive-space-monitoring-gets-an-update-update/. I made a stored procedure that took a database name and a start and end date as input parameters and output the TotalSizeInMB and StatDate columns from the DatabaseDiskStats table. I used that procedure as the datasource for the SSRS report and that was it.

    1. Thanks Shawn, I am giving it a try, I found in code in powershell, where
      MaxDriveSize_in_MB is NULL, should it be not Null, thanks

        1. I’m sorry, but there’s not really one screenshot that will show everything. This isn’t a blog about report writing in SSRS, so if you need help with that, I suggest you look for other resources. You need to set up a data connection, a data source, the report, etc. It’s not something that can be easily done in the comments and it’s not something I’m inclined to write a separate post about. Good luck.

  4. Hello Shaun,
    I make a full backup every night from all of my databases and size of my modellog.ldf is: 285 GB

    Would you please advise me:
    How can I reduce the size of the file?
    How can I stop this issue without changing the backup method

    Thank you in Advance for your time

  5. Alex – What is likely happening is your model database is in full recovery mode and you are not taking transaction log backups for it. A full backup will not clear the transaction log for re-use. Only a log backup will do that. I would recommend taking a transaction log backup of your model database, then shrinking the log file down to a more reasonable size (note that this size with be used as the default for the transaction logs for newly created databases). To stop this from happening again, you have two options going forward: 1) change model db to simple recovery mode or 2) leave it in full recovery mode and begin taking log backups of it. Note that changing it to simple recovery mode means that any new databases created on the server will, by default, be created in simple recovery mode. If this is not what you want, your only option is number 2. Your full backup routine does not need to change.

  6. Hi Shaun,
    Thank you so much for your prompt reply.
    based on your instruction I should run the following commands:
    would you please confirm it?
    Thank you in Advance for your time

    http://www.sqlservercentral.com/Forums/Topic753854-146-1.aspx

    1. ALTER DATABASE {your database} SET RECOVERY SIMPLE;
    2. CHECKPOINT — possibly do this a couple of times to roll the VLF to beginning of file
    3. Shrink the log file using shrinkfile to a reasonable size
    4. ALTER DATABASE {your database} SET RECOVERY FULL;
    5. Perform a full backup now to reset the log chain and allow for transaction log backups

  7. That will work. #2 might require generating some dummy transactions between each checkpoint, but if you are not storing and modifying data in your model database, it probably won’t be necessary. Note that this will cause you to lose the ability to perform a point in time recovery for any time prior to step 4. But again, if you aren’t doing anything in model, this should be ok.

  8. Hello again,
    Thank you so much for your reply.
    Of course it make sense to lose the recovery because I am deleting the logs.
    But I have only two options:
    1- Stop all applications (There is not more than 340 GB free space in the partition)
    2- Losing some recovery points.
    I have no choice other than the second one.
    Really appreciate for your time.

    1. No problem. It sounds to me like something is using the model database. This is a system database and should not be used as an active user database. (Take a quick look – if there are any non-system tables in it, something is using it.) Any new databases created on that server will be a copy of the model database – including any data it contains. This could represent a security risk. You may find my SQL Server 101 course useful. It covers the system databases, what they are used for, and database recovery modes and backup strategies. You can use this link to get 50% off. https://www.udemy.com/microsoft-sql-server-101/?couponCode=RAINBO50 Good luck!

Leave a Reply

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

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.