SQL SERVER – Log File Growing for Model Database – model Database Log File Grew Too Big

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)

About these ads

12 thoughts on “SQL SERVER – Log File Growing for Model Database – model Database Log File Grew Too Big

  1. Very interesting! You should also note that changing the recovery model of the Model database will change the default recovery model of any database that is created after. So if you change Model to SIMPLE mode, and then manually create a database and don’t specify a recovery model, it will be SIMPLE by default.

  2. Hi Pinal,

    This is absolutely correct,there is no point to take full backup of Model on daily basis.

    Unfortunately even our netbackup team was doing it and now it was decided to exclude it.

    Thanks for bringing this up.

    -Anup

  3. I am reminded of an interesting incident at a place I worked and we supported a third party application database on SQL Server 2000 service pack 4. The model database log filled the local drive and SQL Server stopped. hen we tried to start it in single user mode the application would quickly grab a connection. To add to our misery the vendor’s technical support also tried to move the model log file which fortunately they could not. Eventually our Database Manager instructed everyone to stop troubleshooting and opened an isql window ready to run the backup and then started SQL Server in single user mode using isql with the command sqlservr.exe –c –m”isql” –T3608. After the model backup with no log was taken, the SQL Server was back in operation though the vendor could not give us an idea as to what they did to fill the log file.

  4. Hi Pinal,

    I am novice to SQL Server DBA . i have one doubt. Can we connect more then 60000 user in sql server 2005? if, possible what is the mechanism using it. i have studied that maximum 32767 user can connect.

    • Hi Rajendiran,

      SQL server will allow 32767.This is the value returned by select @@max_connections.

      This setting can be changed using sp_configure ‘user connections’.

      I dont think 60000 users can connect simultaneously to sql server.

      -Anup

      • Hi Rajendiran,

        Just a quick correction.I was reading through BOA and found this statement

        ‘Use the user connections option to specify the maximum number of simultaneous user connections allowed on SQL Server. The default is zero, which means that unlimited user connections are allowed.’

        This means that sql server can accept unlimited user connections.

        Hi Pinal,

        Could you please clarify this,and confirm if default user connection value,ie 0 means sql server will accept unlimited connections.

        -Anup

  5. Pingback: SQL SERVER – Creating All New Database with Full Recovery Model Journey to SQLAuthority

  6. Pingback: SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters Journey to SQLAuthority

  7. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s