Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Datafile growth


SQL Server Datafile growth

Author
Message
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 2235
Hello,

I would like to understand the internal michanism of the sql server for Datafile growth.

If datafile set to autogrow with unristricted file growth then SQL Server internally increase the size of the datafile till the disk is not full. Now my question is what is the internal threshold SQL Server is using to check the datafile size and how it grows? i.e. SQL Server will check the internally unallocated space for 95% or 98% or 90%; before increasing the datafile? How SQL Server internally calculating unallocated space?

I am more concern about the what is the threshold value SQL Server will use?

Hope somebody may know the internal things of SQL Server and may help.

Thank you in advance.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 6083
When you create the database, you can decide how much the file should be increased. If you use the GUI the default is 10%. I have to admit that I don’t know how much the default is when you use create database statement and omit the part that tells how to increment each file. In any case this can also be modified and in my opinion it should be modified. If I have a database that is 500GB I wouldn’t want it to get 50GB in the middle of transaction. Most of the time I like to set my file growth to 200MB and monitor it so it wouldn’t get to the situation that it has to use automatic grow. In order to modify it you can run alter database statement (see more details in BOL) or use the GUI (right click on the database, select options from the popup menu and then use the files tab).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 4639
I do not think the information you are asking for is public domain but experience suggests there is not such a threshold.

The most likely scenario is that SQL Server would extend a datafile during the process of extending a segment meaning, if at the time SQL Server needs to allocate space the operation fails because of no more available space then SQL Server appears to check the autoextent option, if set to true then SQL Server extends the datafile as specified then retries allocating the space needed to extend whatever segment was working on.

Please correct me if I'm wrong.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 2235
Hello Paul,

I guess operation may not fail in most of the case but yes it might fail due to unavailability of the space. I guess mostly it get delayed and put on hold unless and untill sql server get the datafile extends.

But what I am looking for is more in depth and internal michanism. Like when insert is happening and page split happen and sql server do not found any space it will extends the datafile. But how sql server make decission to grow the datafile? there might be something like when datafile reaches 95% or 99% it should grow?

Still any indepth knowledge are welcome :-D

Cheers!!!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3097 Visits: 4639
I'm sorry for not being clear.

Let me translate my previous post: 100% :-D

SQL Server would extend a datafile when space is needed and no space is availabe in any available datafile. That's why you don't want to set a small extent parameter 'cause overhead would kill performance.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 2235
Hi Paul,

I guess I am failed to express what I want.

I do understand that in addition to the performance; if small extent is set to autogrow i.e. 250 mb or 500 mb than as autogrow expand the file fregmentation may also increase.

But anyway I am looking some different answer. Like how sql server internally manage the autogrow? I am curious to know what internally happening under the hood(SQL Server) step by stape? e.g. there are lots of in-depth article available with diagram regarding to know how page split happen or how reindxing is happening internally in sql server.

Have a Great weekend!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search