Does the size of the model database increase automatically?

  • invaliddba

    Default port

    Points: 1450

    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.

  • Perry Whittle

    SSC Guru

    Points: 233859

    has someone created objects inside Model by any chance?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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 recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • shump66@yahoo.com

    Right there with Babe

    Points: 761

    The only time I have seen the model database change is when someone added objects to it by accident or when source control software added objects to the model

    Scott
    I am a Senior Data Analyst/DBA
    I work with MS SQL, Oracle & several ETL Tools

    @thewolf66[/url]

  • Adiga

    One Orange Chip

    Points: 27224

    @invaliddba,

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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 recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Adiga

    One Orange Chip

    Points: 27224

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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 recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Adiga

    One Orange Chip

    Points: 27224

    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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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 recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Summer90

    SSC-Dedicated

    Points: 32827

    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.

  • Ivanna Noh

    SSCarpal Tunnel

    Points: 4138

    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 🙂

  • Summer90

    SSC-Dedicated

    Points: 32827

    Thanks. I had a gut feeling that backing up the model db was causing this as well.

  • shaun.stuart

    SSCertifiable

    Points: 6738

    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.

  • Derrick Smith

    SSChampion

    Points: 10833

    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 16 total)

You must be logged in to reply to this topic. Login to reply