Pre allocating free space to db growth

Question

Post reply

Pre allocating free space to db growth

ramana3327

SSCoach

Points: 19722
More actions
April 17, 2022 at 7:58 pm

#4017651

Hi SQL experts,
What are advantages/Disadvantages, if you pre allocate the extra space to the database growth (70-90%) ahead.
How can you release the pre allocated free space? When I choose task shrink file, release unused space. It didn’t release any space even though there’s is 90% free space
Thanks
Ramana

Viewing 11 posts - 1 through 10 (of 10 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Advantages of pre-allocation: Queries don't die when you suddenly run out of room in the middle of a transaction. No need to wait for file growth. No risk of something else stealing the needed diskspace. Possibly avoid disk fragmentation.

Disadvantages: If it's allocated for this, it's not available for something else.

One should almost always over-allocate space (i.e., more than you need right now) depending on growth estimates and available disk space. How much to allocate should be a technical decision, but costs, business decisions, & competing demands may constrain flexibility to allocate what the DBA may consider the ideal amount of space.

Why are you trying to shrink a file and release pre-allocated space? Do you need the space urgently for some other database? Do you believe the estimated growth is drastically high? You should avoid shrinking files whenever possible to avoid index fragmentation & associated impacts to performance from both the process and the index fragmentation.

ramana3327 SSCoach Points: 19722 More actions · Answer 3

I am thinking about the negative consequences of provisioning free space drastically high.

How much difference it will be in backup size and backup time

2. Restoring to the test server. It should have same size as source server. If you pre allocate too much space, the same amount of space is required while restoring.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

ramana3327 wrote:

I am thinking about the negative consequences of provisioning free space drastically high.
How much difference it will be in backup size and backup time
2. Restoring to the test server. It should have same size as source server. If you pre allocate too much space, the same amount of space is required while restoring.

Backups do not store unallocated pages.
The size of the database files that are restored will be restored to their original sizes including free space. If you have "Instant File Initialization" enabled, the file size of data files won't matter much for restores because they'll take so little time. The same is NOT true with transaction log files, which are actually formatted behind the scenes into VLFs or Virtual Log Files. The larger the log file, the longer it will take the restore process to complete. You also have to exercise some caution in what initial size the log file is and the increment used to grow them. If done incorrectly, you have a huge number of VLFs that must be formatted and that can seriously slow down restores. It can also seriously degrade performance for normal operations. See the articles at the following links for a lot more information on all that. I STRONGLY recommend understanding ALL the information written in those articles before you even think of changing what you have and then DO change what you have especially if you've allowed the defaults created by the Model database to prevail when you first created the database OR if you've changed them OR you've preset them to something else OR you've got old databases that have been around for a long time.

http://3.209.169.194/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

ratbak wrote:

Advantages of pre-allocation: Queries don't die when you suddenly run out of room in the middle of a transaction. No need to wait for file growth. No risk of something else stealing the needed diskspace. Possibly avoid disk fragmentation. Disadvantages: If it's allocated for this, it's not available for something else.
One should almost always over-allocate space (i.e., more than you need right now) depending on growth estimates and available disk space. How much to allocate should be a technical decision, but costs, business decisions, & competing demands may constrain flexibility to allocate what the DBA may consider the ideal amount of space.
Why are you trying to shrink a file and release pre-allocated space? Do you need the space urgently for some other database? Do you believe the estimated growth is drastically high? You should avoid shrinking files whenever possible to avoid index fragmentation & associated impacts to performance from both the process and the index fragmentation.

Very well stated. I also add that, unless you've deleted a shedload of data that frees up more than, say, 20% of the original file size, then leave it alone because it's probably<insert drum roll here> just going to quickly grow to that same size again because of things like the way people don't actually know how to do index maintenance on either the (especially) large indexes on large tables or the "bazillions" of small indexes on smaller tables. They also don't actually look or even know about things like "Trapped Short Rows" due to the default of "InRow LOBS", etc, etc, that can easily wast 40% of your disk space. People also do some pretty whack-o stuff like converting their GUIDs to NVARCHAR(36).

Unless you're going to make the investment to understand and fix that kind of stuff (which can seriously help performance even if you have enough harddisk and RAM, as well), shrinking files is a serious waste of time. Go buy some more hard disk and RAM, instead.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

One of the best advantages to having extra space available in the data file is that space can be utilized for index maintenance. If you don't have enough space in the data file for that index being rebuilt - SQL Server attempts to grow the file. If you are then shrinking that file - SQL Server is undoing that index maintenance as it moves pages. You then get into this cycle of growing/shrinking and just wasting processing time.

For any server that is dedicated to SQL Server - there is no reason to worry about leaving space available on those drives for processes outside of SQL Server. The only thing you need to worry about having extra space available for is for integrity checks - which really means you need to have some space available on each drive above what is allocated to the data files. For example, if your drive is 1TB then you would leave at least 100GB of space available on that drive.

With that said - you don't want to over-allocate space either. What you need to do is determine the average growth rate - based on the activity in that system. Project that over the next 6 to 12 months (or more) to determine how much space you will need. Then - determine how much space you have available and grow the files out to support that growth. After that, you can monitor and manually grow the files out as needed - and be prepared to request additional storage long before you start to run into space issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Jeffrey Williams wrote:

What you need to do is determine the average growth rate - based on the activity in that system. Project that over the next 6 to 12 months (or more) to determine how much space you will need.

I did that by looking at backup history system tables to see the size of the backup files over time. I think there's a setting somewhere for deleting backup history, which I changed to extend it. (Don't really remember)

This reply was modified 2 years, 7 months ago by homebrew01.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 8

To avoid using the user db for index rebuild work space, look into specifying SORT_IN_TEMPDB = ON as part of the index REBUILD. This is especially true if you have dedicated drives for tempdb and/or tempdb is SSD or some other very fast storage type vs. your main db.

By moving the work i/o out of the main db, you can also most often speed up the load and increase contiguousness of the final index.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

That's a good idea but I don't believe that will reduce the impact on the log file when you use REORGANIZE.

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 10

Right, because reorganize only ever needs 1 extra work page, the in_tempdb option is really meaningless for reorg.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".