Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Space Available
- Thread starter eggy168
- Start date
mrdenny
Programmer
- May 27, 2002
- 11,595
That is what is supose to happen. You are shrinking the database, which removes the free space from the file, and returns it to the OS.
You can make the file larger in the database properties window, or by writting data to the database, which will make the database extend it self.
Denny
--Anything is possible. All it takes is a little research. (Me)
You can make the file larger in the database properties window, or by writting data to the database, which will make the database extend it self.
Denny
--Anything is possible. All it takes is a little research. (Me)
- Thread starter
- #3
Here's some info in a nutshell... (it obviously is much deeper than just this)
SQLServer allocates chunks of areas called pages to hold the database information. When a database is defined, the system defaults to setting up 10% freespace. That's 10% of freespace within each new page setup for data. What that does is allow SQLServer to insert data to 90% of the individual data page chunks. That allows subsequent insertions to be attempted right within the given page where the [new] data is most appropriate.
When there is not enough freespace to satisfy a given insertion requirement, a new 'page' is allocated and the new entry is added within that page. Just like operating system file allocations, this can lead to significant fragmentation of what would otherwise be contiguous data.
So, some freespace is suggested. And a DBA will make calculatinos, based on the purpose of a database as to what that value should be.
As a rule, the 10% default is probably sufficient to start with. Later on you can do analyses to decide if other values could be more effective.
Make sure that you specify that the database can 'grow' automatically. (The default on SQL2000 and I think 7 as well). Then SQLServer will always 'grow' your database to the limitations of your disk (by simply adding more pages as required). So its not like you have to worry that it will 'run out' of freespace.
SQLServer allocates chunks of areas called pages to hold the database information. When a database is defined, the system defaults to setting up 10% freespace. That's 10% of freespace within each new page setup for data. What that does is allow SQLServer to insert data to 90% of the individual data page chunks. That allows subsequent insertions to be attempted right within the given page where the [new] data is most appropriate.
When there is not enough freespace to satisfy a given insertion requirement, a new 'page' is allocated and the new entry is added within that page. Just like operating system file allocations, this can lead to significant fragmentation of what would otherwise be contiguous data.
So, some freespace is suggested. And a DBA will make calculatinos, based on the purpose of a database as to what that value should be.
As a rule, the 10% default is probably sufficient to start with. Later on you can do analyses to decide if other values could be more effective.
Make sure that you specify that the database can 'grow' automatically. (The default on SQL2000 and I think 7 as well). Then SQLServer will always 'grow' your database to the limitations of your disk (by simply adding more pages as required). So its not like you have to worry that it will 'run out' of freespace.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question