After reading my earlier article SQL SERVER – master Database Log File Grew Too Big, I received an email recently from another reader asking why does the log file of model database grow every day when he is not carrying out any operation in the model database. As per the email, he is absolutely sure that he is doing nothing on his model database; he had used policy management to catch any T-SQL operation in the model database and there were none.

This was indeed surprising to me. I sent a request to access to his server, which he happily agreed for and within a min, we figured out the issue. He was taking the backup of the model database every day taking the database backup every night. When I explained the same to him, he did not believe it; so I quickly wrote down the following script. The results before and after the usage of the script were very clear.

What is a model database?

The model database is used as the template for all databases created on an instance of SQL Server. Any object you create in the model database will be automatically created in subsequent user database created on the server.

NOTE: Do not run this in production environment.

During the demo, the model database was in full recovery mode and only full backup operation was performed (no log backup).

Before Backup Script

Backup Script in loop

DECLARE @FLAG INT
SET @FLAG = 1
WHILE(@FLAG < 1000)
BEGIN
BACKUP DATABASE [model] TO  DISK = N'D:\model.bak'
SET @FLAG = @FLAG + 1
END
GO
After Backup Script

Why did this happen?

The model database was in full recovery mode and taking full backup is logged operation. As there was no log backup and only full backup was performed on the model database, the size of the log file kept growing.

Resolution:

Change the backup mode of model database from “Full Recovery” to “Simple Recovery.”.

Take full backup of the model database “only” when you change something in the model database.

Let me know if you have encountered a situation like this? If so, how did you resolve it? It will be interesting to know about your experience.

Reference: Pinal Dave (http://blog.SQLAuthority.com)