June 26, 2012 at 3:46 am
Hi All,
Why database files grow physically even there is free space in the files.
Like if we have a data file physically with 10GB insize and free space in that data file is around 9.5GB.
After 1 hour I have checked the data file size physically it 12Gb and free space in that file is around 11GB.
Why this behaviour. If the dta is getting inserted into that database whyit doenot use the free space in that data file ?
Thanks and Regards!!
Thank You.
Regards,
Raghavender Chavva
June 26, 2012 at 4:47 am
Assuming we're talking about a data file and not a log file, something had to have filled that space. Do you have load processes or queries that use tables for interim storage and then drop/truncate them? You won't see files grow unless space was needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2012 at 12:41 pm
How are you checking the database size?
If you are right-clicking the database and selecting properties to see the database size, that size includes the size of the transaction log and the available space in both the data file and log file.
If the database is in full recovery model and you are not performing frequent transaction log backups then you will see this grow continually as the transaction log fills up and grows.
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
June 26, 2012 at 12:51 pm
Grant Fritchey (6/26/2012)
Assuming we're talking about a data file and not a log file, something had to have filled that space. Do you have load processes or queries that use tables for interim storage and then drop/truncate them? You won't see files grow unless space was needed.
Grant, wouldn't the free space on a page stay the same as the file grew? i.e. I keep 10% free on each page, file grows, still 10% free on a larger file, larger mb free?
Jared
CE - Microsoft
June 26, 2012 at 12:56 pm
SQLKnowItAll (6/26/2012)
Grant Fritchey (6/26/2012)
Assuming we're talking about a data file and not a log file, something had to have filled that space. Do you have load processes or queries that use tables for interim storage and then drop/truncate them? You won't see files grow unless space was needed.Grant, wouldn't the free space on a page stay the same as the file grew? i.e. I keep 10% free on each page, file grows, still 10% free on a larger file, larger mb free?
It's possible, but usually, I'll see a 10% growth, in response to a need for space, then space is filled in, but the growth and the space used don't coincide, so it's not likely that the free space will be constant.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2012 at 1:06 pm
Grant Fritchey (6/26/2012)
SQLKnowItAll (6/26/2012)
Grant Fritchey (6/26/2012)
Assuming we're talking about a data file and not a log file, something had to have filled that space. Do you have load processes or queries that use tables for interim storage and then drop/truncate them? You won't see files grow unless space was needed.Grant, wouldn't the free space on a page stay the same as the file grew? i.e. I keep 10% free on each page, file grows, still 10% free on a larger file, larger mb free?
It's possible, but usually, I'll see a 10% growth, in response to a need for space, then space is filled in, but the growth and the space used don't coincide, so it's not likely that the free space will be constant.
Ok, super simplified example... let's assume that I have 1 page of data with 6kb used, not a heap, and a fill factor of 75%. So, (again super simplified) I would say that the next row inserted will be placed on the next page. Before that row is inserted, we have 25% free space with a total of 2kb free. Right?
Assuming that is correct, I insert the row (1kb let's say) and we now have a new page of 8kb with only 1kb used. File grew by 8kb, free space grew by 7kb on top of the 2 from the previous page. Is all of this correct?
Jared
CE - Microsoft
June 26, 2012 at 1:25 pm
SQLKnowItAll (6/26/2012)
Grant Fritchey (6/26/2012)
SQLKnowItAll (6/26/2012)
Grant Fritchey (6/26/2012)
Assuming we're talking about a data file and not a log file, something had to have filled that space. Do you have load processes or queries that use tables for interim storage and then drop/truncate them? You won't see files grow unless space was needed.Grant, wouldn't the free space on a page stay the same as the file grew? i.e. I keep 10% free on each page, file grows, still 10% free on a larger file, larger mb free?
It's possible, but usually, I'll see a 10% growth, in response to a need for space, then space is filled in, but the growth and the space used don't coincide, so it's not likely that the free space will be constant.
Ok, super simplified example... let's assume that I have 1 page of data with 6kb used, not a heap, and a fill factor of 75%. So, (again super simplified) I would say that the next row inserted will be placed on the next page. Before that row is inserted, we have 25% free space with a total of 2kb free. Right?
Assuming that is correct, I insert the row (1kb let's say) and we now have a new page of 8kb with only 1kb used. File grew by 8kb, free space grew by 7kb on top of the 2 from the previous page. Is all of this correct?
Not exactly. Fill factor doesn't maintain a precise space on each page. It creates a space when the index is created or rebuilt that allows for future expansion to reduce page splits. So adding 1kb to that thing that has 2kb free means you're going to fill up 1k of the 2kb remaining.
Plus, data file expansion has to do with the settings there, not free space settings on indexes. So if you're out of room and a single page is requested, SQL Server will go to the default value, 1mb, or, to whatever you specified as your growth rate, percentage or hard coded value, and add that much. If you specified a percentage growth, you'll get that, but, you're also going to fill in part of it with the amount that was needed beyond what was specified.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 26, 2012 at 1:27 pm
System would insert the record into the existing page. SQL Server won't create a new page just because you are 75% full on the page. If the record fits, it is added. You get a new page when SQL Server can't fit a record on a page and it has to split the page.
June 26, 2012 at 1:30 pm
Ah yes! So If I add only a small amount of data, but it is more than is available... The file will grow by the amount specified. When people leave it at the default value (10% I think), it can cause major issues if you already have a large relatively static database and add a small amount of data; i.e. 300gb database at 10%file growth, add 2mb of data to a full file, you now have a 330gb file with essentially 30gb free.
Jared
CE - Microsoft
June 26, 2012 at 1:55 pm
SQLKnowItAll (6/26/2012)
you now have a 330gb file with essentially 30gb free.
Which is probably a good thing, much better than a 300 GB file with 30 MB free.
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
June 26, 2012 at 3:09 pm
GilaMonster (6/26/2012)
SQLKnowItAll (6/26/2012)
you now have a 330gb file with essentially 30gb free.Which is probably a good thing, much better than a 300 GB file with 30 MB free.
Very true... I guess I just wanted to demonstrate that you can see lots of free space after growth. Thanks Gail!
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply