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
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 (https://blog.sqlauthority.com)
9 Comments. Leave new
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.
Hi Jason,
This will be little risky,it is always good not to change Model database recovery model.
-Anup
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
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.
good article, just curious, why would one set the recovery to full on a model database.
It is common practice to configure the model database to the desired settings of any new databases. Since full recovery is desired on all production server user databases, we set model to full recovery.
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