asked
Monkey business in model database?
I've been reviewing the number of transaction log VLFs on our servers using Paul Randal's script ([
http://www.sqlskills.com/BLOGS/PAUL/post/Survey-how-are-your-transction-logs-configured.aspx][1]) and I noticed something odd. On one of the servers (2008 R2 Std Ed) the model database has a 200MB+ log file (98% used) with 163 VLFs. It looks like there's been some activity in there that has caused quite a lot of log growth but I can't imagine what. We don't have user defined data types, CLR objects, or anything like that so I would expect the model database to be as new. On my own machine (2008 R2 Dev Ed) model has 0.74MB of log with 3 VLFs. Interestingly, other servers all vary a bit. Any ideas what's going on or how I could investigate, please? [1]:
http://www.sqlskills.com/BLOGS/PAUL/post/Survey-how-are-your-transction-logs-configured.aspx
http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx If you are performing backups against "all system databases" this will include the model database. The backup transaction is logged in the log file of the database. So over time the model log file would grow and in turn cause additional VLFs. This will occur since most instances the model database default recover model is set to FULL. I usually set the recovery model to SIMPLE on model, because I want it that way for new databases, but that would also control the log growth since I back it up regularly.
Doh! Of course. This is our core production server and I'm backing up the system databases every hour, model included, and model is on the full recovery model. I'll clear the log and change it to simple recovery. Thanks, Melton.
