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), the
n
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 owne
d 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 size
s
> 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), t
hen
> 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 sh
ow
> in the database free space (as obviously 2GB unused space can't fit in thi
s
> 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 ow
ned 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 p
ages will add to the
unused value. I believe that only not allocated pages on uniform extents wil
l. Check out the source
code for sp_spaceused (etc.) to be certain. I guess that an index rebuild (o
r 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:
>
>
> 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? Ha
s
> 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
Showing posts with label filegroups. Show all posts
Showing posts with label filegroups. Show all posts
Sunday, March 25, 2012
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
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
Thursday, March 22, 2012
Database filegroups
Hi,
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file?
Thanks
Phil
It's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file?
> Thanks
> Phil
sql
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file?
Thanks
Phil
It's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file?
> Thanks
> Phil
sql
Database filegroups
Hi,
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file'
Thanks
PhilIt's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file'
> Thanks
> Phil
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file'
Thanks
PhilIt's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file'
> Thanks
> Phil
Database filegroups
Hi,
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file'
Thanks
PhilIt's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx
.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file'
> Thanks
> Phil
I have a server with an external storage with 300GB. I
have 2 logical drives assign to the storage, one for logs
and other to the database files. My develop team says that
in a year the new database will grow to about 200GB. I'm
considering allocate one mdf file of 200GB, what should i
do? create a lot of filegroups and spread the database or
allocate one simple mdf file'
Thanks
PhilIt's really impossible to know without a lot more information.
In general... unless I have a strong reason to do other wise... I simply
have a single filegroup and put one file on each logical drive (especially
if the logical drive is seperate spindles..). An exception might be that I
might consider multiple files on the same drive if the logical drive is a
SAN or something with LOT's of IO capacity.
Unless I'm dealing with very large data sets I normally don't worry about
multiple file groups unless I'm doing it for management or recovery reasons.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil" <anonymous@.discussions.microsoft.com> wrote in message
news:158cc01c41d83$bf076270$a001280a@.phx
.gbl...
> Hi,
> I have a server with an external storage with 300GB. I
> have 2 logical drives assign to the storage, one for logs
> and other to the database files. My develop team says that
> in a year the new database will grow to about 200GB. I'm
> considering allocate one mdf file of 200GB, what should i
> do? create a lot of filegroups and spread the database or
> allocate one simple mdf file'
> Thanks
> Phil
Subscribe to:
Posts (Atom)