Database free space vs. Table unused space

Hi,
I've had a query from an analyst regarding database free space and table
unused space. We have a database made up of 3 filegroups; with total sizes
of 100GB, 25GB, & 27GB , and free space is reported as 65MB, 70MB, & 2MB
respectively.
Now, if he executes "sp_spaceused" against one specific table that he's
working with (trying to reduce size by improving purging routines, etc), then
it reports the data and index sizes, but also reports over 2GB of "unused
space". This is after running an updateusage, etc.
What is this referring to? Can it be freed to the OS? Why does it not show
in the database free space (as obviously 2GB unused space can't fit in this
database's free space)? Any light shed on this would be appreciated.
Thanks,
daveThe unused value reported by sp_spaceused are pages on extents that are owned by that particular
objects, but the pages are currently not in use. So the pages can be used by the object that owns
the extent in question but not by other extents.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DavidCur" <cdf_nospam_@.webmail.co.za> wrote in message
news:2F6AA526-E215-4EC7-AEBA-7C454F937FC4@.microsoft.com...
> Hi,
> I've had a query from an analyst regarding database free space and table
> unused space. We have a database made up of 3 filegroups; with total sizes
> of 100GB, 25GB, & 27GB , and free space is reported as 65MB, 70MB, & 2MB
> respectively.
> Now, if he executes "sp_spaceused" against one specific table that he's
> working with (trying to reduce size by improving purging routines, etc), then
> it reports the data and index sizes, but also reports over 2GB of "unused
> space". This is after running an updateusage, etc.
> What is this referring to? Can it be freed to the OS? Why does it not show
> in the database free space (as obviously 2GB unused space can't fit in this
> database's free space)? Any light shed on this would be appreciated.
> Thanks,
> dave|||"Tibor Karaszi" wrote:
> The unused value reported by sp_spaceused are pages on extents that are owned by that particular
> objects, but the pages are currently not in use. So the pages can be used by the object that owns
> the extent in question but not by other extents.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Yes, I understand that - but I wanted to know if there was a way to free
that unused space up? What governs how much unused space a table has? Has
it got something to do with the FILLFACTOR of indexes? I'm just trying to
understand how this table can have so much (>2GB) unused space.
Thanks,
dave|||Higher fillfactor will mean less pages. But I don't think that less fuller pages will add to the
unused value. I believe that only not allocated pages on uniform extents will. Check out the source
code for sp_spaceused (etc.) to be certain. I guess that an index rebuild (or reorg) can less the
unused value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DavidCur" <cdf_nospam_@.webmail.co.za> wrote in message
news:EDD26B2C-1B3C-43AF-911D-2BAD4486C373@.microsoft.com...
> "Tibor Karaszi" wrote:
>> The unused value reported by sp_spaceused are pages on extents that are owned by that particular
>> objects, but the pages are currently not in use. So the pages can be used by the object that owns
>> the extent in question but not by other extents.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>
> Yes, I understand that - but I wanted to know if there was a way to free
> that unused space up? What governs how much unused space a table has? Has
> it got something to do with the FILLFACTOR of indexes? I'm just trying to
> understand how this table can have so much (>2GB) unused space.
> Thanks,
> davesql