Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Unused Space and Unallocated space

Author  Topic 

Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-28 : 11:15:58
I am not quite understanding what unallocated space in sp_spaceused procedure is trying to indicate. Does it mean that the database needs the space and it has not been allocated? For ex it shows me a value of -1116 KB!
Also I can understand that my data and index sizes increase daily but why should the UNUSED space also increase? When I created my database, I had set File Growth to increase 1000 MB in space for both data and log files.

Almighty SQL Goddess

38200 Posts

Posted - 2003-03-28 : 14:55:07

Your questions are so hard! You might want to see if this information is in one of the books that you have (like Inside SQL Server which goes into detail about this kind of stuff, but not sure if it goes into detail about this stuff specifically). I have no idea what unallocated could mean when you get a negative number. It just doesn't make any sense. Have you set the @updateusage parameter to true so that it updates its information so that it can report back to you the correct information? Setting the parameter to true is the same thing as running DBCC UPDATEUSAGE.

Go to Top of Page

Constraint Violating Yak Guru

265 Posts

Posted - 2003-03-28 : 19:24:31
What to do, it is hard to ignore some details and take the rest because it serves our purpose. I am having a hard time because if I don't get convinced about why I need certain amount of space, how can I convince mgmt to spend money on more hard-disk? Because I have only 1 yr of DBA exp, I want to make sure I know what I say.
I don't understand why spaceused values will be different than dbcc's showcontig wrt data pages.

Go to Top of Page

SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-03-29 : 16:39:33
Unallocated space is exactly what it says, the sum of the space used by extents that are not yet allocated to any database objects. So basically it's the size of the database file(s) minus the sum of the space used by all extents that are assigned to objects be they data,index,GAM,IAM,SGAM,PFS,BCM or DCM pages. Unused space is the sum of free space available on pages in the data file(s) that have been allocated to an object or objects. Thus running sp_spaceused on a table for instance, the sum of the data and index page space plus the unused space will give you the total space (reserved column in the resultset). As rows are inserted,updated and deleted in a table then in almost all circumstances there will be some free space on each page. As deletes and updates occur, plus inserts into a table with a clustered index where inserts may not be in clustered index order, then there may well be readjustment of space on the pages e.g. page splits, ghost records etc. Some of this space may be reused but some of it may not, thus as there is transactional activity in your database , the unused space will tend to increase along with the data and index space. This is where defragmenting your indexes can come into play in terms of compacting this unused space and making your data pages and indexes more efficent. You can determine the average freespace on a page for an object using dbcc showcontig. In almost all circumstances your row size will leave some free space on a page thus what you are seeing is to be expected. As indicated by Tara, setting the @updateusage parameter of sp_spaceused to true will update the space allocation values in sysindexes which when they get out of sync can lead to anomalous results like those you observed.

Jasper Smith


Edited by - jasper_smith on 03/29/2003 17:08:00
Go to Top of Page

- Advertisement -