Kaseya Community

FYI - SQL Database / Auto Growth

  • Hiya,

    Just for anybody who may not know as I did not - in SQL 2005 / 2008 from what I have been told the default database autogrowth setting is 1mb. This means that as your db grows it increases in size by 1mb each time. The impact here is that if you have a 5gb file it will have grown in 1mb blocks over a period of time so fragmentation will be quite bad. I recently shut down my DB and defragged all my kaseya drives, I then set the auto growth to 20% so that it should increase by 20% at the next growth cycle.

    I cannot say for definite that I have noticed a huge performance increase but I definitely have noticed an improvement.

    I will also be reguarly looking at the defrag status of my disks on my kaseya server.

    Just thought I would share this bit of info.

    Michael

    Legacy Forum Name: FYI - SQL Database / Auto Growth,
    Legacy Posted By Username: mmartin
  • FWIW, autogrowth is generally a bad thing. Set the database size to a fixed, large value and check to ensure that it is in one large chunk. Then if you need more space, you can manually grow it and defrag as necessary.

    Also, growing a 5GB database by 20% will cause a signficant performance hit, as it takes a very long time (relatively, of course) to grow a file by 1GB ...

    Legacy Forum Name: How-To,
    Legacy Posted By Username: Lmhansen
  • Agreed general practice seems to be do it manually but I am not monitoring that actively so i am happy to let it grow itself.

    Also the growth over the next while will be small so 1 gb will cover me for a while and then if needs be some performance hit for a short period to get another 1 gb is acceptable. At least fragmentation will be greatly reduced.

    Legacy Forum Name: How-To,
    Legacy Posted By Username: mmartin