Have you ever wondered why the log file of the model database in SQL Server keeps growing, even when you’re not performing any operations on it? This is a question that was recently asked by one of our readers, and it sparked our curiosity to investigate further.

The model database in SQL Server serves as a template for all databases created on an instance of SQL Server. Any object created in the model database will be automatically created in subsequent user databases on the server. This makes it an important database to understand and manage.

In the case of our reader, he was certain that he wasn’t performing any operations on the model database. He had even used policy management to track any T-SQL operations on the database, and there were none. So why was the log file of the model database growing every day?

After gaining access to his server, we quickly identified the issue. It turned out that he was taking a backup of the model database every day. This backup operation, even though it was a full backup, was a logged operation. Since there were no log backups being performed, the log file of the model database kept growing.

To demonstrate this, we wrote a simple script that performed multiple full backups of the model database in a loop. The results were clear – the log file size increased with each backup operation.

So, how can you resolve this issue if you encounter it? The solution is to change the backup mode of the model database from “Full Recovery” to “Simple Recovery.” This will prevent the log file from growing excessively. Additionally, it is recommended to only take a full backup of the model database when you make changes to it.

Have you ever encountered a situation like this? If so, how did you resolve it? We would love to hear about your experiences and any other tips you have for managing the model database in SQL Server.

Thank you for reading!