Does the size of the model database increase automatically?
-
When I first created instance of sql server 2008 , the model database primary data file was 1.75 MB and log file was 0.16 MB.
Over time I created several databases and backups. The size of the model db is 80 MB. I wonder how the size increased ?
any links to read up on this topic or information is greatly appreciated.
-
has someone created objects inside Model by any chance?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
If model is growing, someone or something is creating and using objects in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Is the data file size 80 MB or Transaction Log size? If the transaction log size is growing, I suspect you would have scheduled a FULL backup for model database and there is no Transaction Log backup scheduled.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga -
Adiga (11/28/2010)
I suspect you would have scheduled a FULL backup for model database and there is no Transaction Log backup scheduled.Recovery model not withstanding, model won't grow (data or log) unless something is using it. Typically nothing should use model. It's just there as a template for new databases (and tempDB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/28/2010)
Adiga (11/28/2010)
I suspect you would have scheduled a FULL backup for model database and there is no Transaction Log backup scheduled.Recovery model not withstanding, model won't grow (data or log) unless something is using it. Typically nothing should use model. It's just there as a template for new databases (and tempDB)
Agreed. Assuming that the model database recovery model is still FULL, Taking a FULL backup without a transaction log backup would cause the transaction log to grow.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga -
Adiga (11/28/2010)
Taking a FULL backup without a transaction log backup would cause the transaction log to grow.No it won't. Taking a full backup, making changes to the database and not taking tran log backups causes the tan log to grow. It won't grow by itself if no changes are happening and Model should not get any changes.
In the case of Model it's not the full backups and no log backups that's the problem, that's how the backups for Model have been set up for just about every system I've ever worked with. The problem is that something is making changes to model, which should not happen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster
No it won't. Taking a full backup, making changes to the database and not taking tran log backups causes the tan log to grow. It won't grow by itself if no changes are happening and Model should not get any changes.
In the case of Model it's not the full backups and no log backups that's the problem, that's how the backups for Model have been set up for just about every system I've ever worked with. The problem is that something is making changes to model, which should not happen.
With all respect I have to disagree with this. I just did a repro. The transaction log size increased from 1 MB to 30 MB by just initiating FULL backup continuously on model database. No new tables were added or data in the tables got changed.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga -
If you want to nitpick...
Full backups log the checkpoint start and end, the changes to the database boot page and a couple other things.
To grow a log that starts with default settings (1MB, 10%) to 30MB by full backups alone requires around 2300 full backups. At one backup a day (about the most I've ever seen for model backups) that's close on 6 and a half years of backups. At one backup a week (the most frequent that I would suggest), that's around 44 years of backups.
I stand by my original comment. If you are seeing model (visibly) grow, something is using the database - creating objects, modifying data, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oddly enough after reading this post and checking all of our SQL2005 and 2008 SQL Servers I have noticed the log file for model growing as well. The nice thing is one of the reports in Mgt Studio show the date/time and size it grows by. I don't understand why it is growing either. We do NOT add anything or touch the model database. All we do is do a full backup of the model database nightly/automatically via db backup script and it is very odd that ALL of our SQL Servers model logs are growing, not just one or two.
-
I have the same issue - the model database log file grows occasionally. This happens on all our SQL servers. On one instance, the model data file has grown as well (by a very small amount). Log growth appears to be every 3 or 4 months. the model database is never used and contains no 'extra' user tables, or other objects.
Apparently model log file growth can be caused by backing up the model database (which we do):
http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx
I know this is an old thread - but the link may help someone faced with the same issue 🙂
-
Thanks. I had a gut feeling that backing up the model db was causing this as well.
-
Just to chime in with my experience. I was seeing the same thing. I even left a trace running on the model db for a week and the only activity it captured was the backups. I'm taking a differential backup daily and a full backup weekly. My model transaction log was growing. I added the model db to my t-log backup job and the growth stopped. I didn't grab numbers of how much it was growing each time for each server, but I'm set up to grow by a fixed amount - which varies from a few MB to tens of MB, depending on the server. Here's a graph showing the growth on one of my servers. As you can see, this one is set to autogrow by 2 MB. There are no user objects in the database and this is the one I was monitoring for a week and saw no activity.
I only found this because I have another job that checks for VLFs in logfiles and one day, my model db showed up as having more than 50 VLFs.
-
I noticed my model log sizes growing too..this was because I had automated jobs running that cycled through every database checking fragmentation, size, backups, etc...any 3rd party monitoring tools would probably have the same impact and create some kind of transaction in the logs at some point.
This hasn't happened since I set all of my model databases to Simple, in any case.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply