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:
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.