DB Autogrowth

Question

DB Autogrowth

Viewing 2 posts - 16 through 16 (of 16 total)

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

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 1

DHeath - Wednesday, August 8, 2018 3:07 PM
Is this a warehousing database that grows at the 40GB or a OLTP databases maybe a database strictly for reports? The way the db is used should also be part of the equation as to how you grow it. Ideally you grow the DB as few times as possible to decrease the fragmentation and VLF's, you can grow it monthly with the likes of 200GB that gives you the control and know exactly how much you are growing by over percentages and just 12 growth spurts a year... LOL. Just my .02 🙂
Good Luck
D

Keeping the VLF's to a min is advantageous. Creating a job that runs at night to check the free space and then increase the DB size could add some control to the growth of the DB.

Jeff Moden - Thursday, August 9, 2018 12:08 PM
No, frequency of restores and autogrowth settings doesn't matter at all during restores. If you have, for example, a Terabyte of unused or unallocated space in your production database, the restore will build that same amount of the freespace in the lesser environment. If there isn't enough room for that, as discovered by the prechecks that SQL Server does, it will simply not allow the restore. And don't forget that if the poo hits the fan in a DR situation and you have to sacrifice some other lesser machine to act as your production box, it may not fit and then the fan also turns to poo. 😀

A very Good point. Not many people take DEV, TEST Pre-Prod or Prod copies into consideration and only worry about production. Then when you need to move the data around to another location it becomes a massive project.

If you are moving in 40GB worth of data in and out a week. you could look at partition switching to move data in and out of the table more easily.

Have you considered compression if the data is very repetitive.
cheers
Jacques

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

SQL Adventurar - Thursday, August 9, 2018 5:56 PM
DHeath - Wednesday, August 8, 2018 3:07 PM
Is this a warehousing database that grows at the 40GB or a OLTP databases maybe a database strictly for reports? The way the db is used should also be part of the equation as to how you grow it. Ideally you grow the DB as few times as possible to decrease the fragmentation and VLF's, you can grow it monthly with the likes of 200GB that gives you the control and know exactly how much you are growing by over percentages and just 12 growth spurts a year... LOL. Just my .02 🙂
Good Luck
D
Keeping the VLF's to a min is advantageous. Creating a job that runs at night to check the free space and then increase the DB size could add some control to the growth of the DB.

Just a bit of a sidebar... You can actually have too few VLFs. Google for "Kimberly Tripp VLF" to find the article.

Two other things... If you have instant file initialization enabled, it takes nearly zero time even for relatively large growth settings on the MDF and NDF files. Ordinarily, though, people's index maintenance routines will cause growth of freespace. Also, if you're log file is growing enough where you have to schedule a job to do the growth overnight or even monthly growth, then you have a serious code problem (which could also be caused by index maintenance routines) that needs to be fixed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)