reclaim "unused space" from a table?

We have a table that is showing a large amount of "reserved" space:
Rows: 4095513
Reserved 11255328 KB
Data: 1176296 KB
Index_size: 531240 KB
unused: 9547792 KB
(found wiht sp_spaceused)
We have only simple types in this table... no ntext or blobs or
anything.
Just int, smalldatetime, varchar(50) etc. (only 10 fields)
So our data and indices come to about 1.6 GB, but "unused" space is 9.1
GB.
We are running out of drive space...why would it reserve so much space
for this table?"cmay" <cmay@.walshgroup.com> wrote in message
news:1139343553.343130.288690@.g43g2000cwa.googlegroups.com...
> We have a table that is showing a large amount of "reserved" space:
> Rows: 4095513
> Reserved 11255328 KB
> Data: 1176296 KB
> Index_size: 531240 KB
> unused: 9547792 KB
> (found wiht sp_spaceused)
> We have only simple types in this table... no ntext or blobs or
> anything.
> Just int, smalldatetime, varchar(50) etc. (only 10 fields)
> So our data and indices come to about 1.6 GB, but "unused" space is 9.1
> GB.
> We are running out of drive space...why would it reserve so much space
> for this table?
>
What did you specify when you created the database? Take a look at DBCC
SHRINKFILE.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
The DB was created like 10 years ago, so I have no idea what happened
back then.
The DB is set to auto grow by 10%. From what I have read shrinking the
DB won't solve this problem. The DB is 15 GB with just about all of it
being considered "used" (I could get back a couple hundred MB by
shrinking the DB), but the 9GB of space being eaten up by this one
table is what is really painful.
I tried running DBCC CleanTable but that didn't help either.
I'm going to try to insert all the data into a new table and then drop
the old one, but that isn't really ideal.
If there are any other things I can try I would love to give it a go.|||Can you try rebuilding the clustered index? That will automatically move all
the data to new space.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cmay" <cmay@.walshgroup.com> wrote in message
news:1139354564.333651.258690@.g14g2000cwa.googlegroups.com...
> Rick,
> The DB was created like 10 years ago, so I have no idea what happened
> back then.
> The DB is set to auto grow by 10%. From what I have read shrinking the
> DB won't solve this problem. The DB is 15 GB with just about all of it
> being considered "used" (I could get back a couple hundred MB by
> shrinking the DB), but the 9GB of space being eaten up by this one
> table is what is really painful.
> I tried running DBCC CleanTable but that didn't help either.
> I'm going to try to insert all the data into a new table and then drop
> the old one, but that isn't really ideal.
> If there are any other things I can try I would love to give it a go.
>|||We actually didn't have a clustered index on this table.
I ended up running an insert from the offending table to a new table,
then dropped the old one, and the new one looks great.|||In general all tables should have a clustered index. You have no way to
control fragmentation or issues like this without one.
--
Andrew J. Kelly SQL MVP
"cmay" <cmay@.walshgroup.com> wrote in message
news:1140532518.546041.33250@.z14g2000cwz.googlegroups.com...
> We actually didn't have a clustered index on this table.
> I ended up running an insert from the offending table to a new table,
> then dropped the old one, and the new one looks great.
>

Rebuild of SQL Server

With the initial system DISK configuration now running out of space, it will
be necessary to reconfigure the disk structure - with a reload of Win2k and
SQL 2000.
Question: Prior to the server rebuild, if SQL is stopped and system/user d
atabase device files are copied to tape, after the rebuild of the server (sa
me name) and a reload of SQL 2000 to same drive, would it possible to stop S
QL and copy the same databa
se device files from tape to avoid a SQL restore of the databases. This woul
d save some time and effort in recreating the databases from scratch, includ
ing the master database.
Thanks in Advance for any advice.Hi,
Yes, You can do that and it will be the faster and easy methodology. But
ensure that you have the same service pack in new server as well.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Greg H" <anonymous@.discussions.microsoft.com> wrote in message
news:F5F90AF5-EA63-4192-BEB5-E4238C2A624F@.microsoft.com...
> With the initial system DISK configuration now running out of space, it
will be necessary to reconfigure the disk structure - with a reload of Win2k
and SQL 2000.
> Question: Prior to the server rebuild, if SQL is stopped and system/user
database device files are copied to tape, after the rebuild of the server
(same name) and a reload of SQL 2000 to same drive, would it possible to
stop SQL and copy the same database device files from tape to avoid a SQL
restore of the databases. This would save some time and effort in recreating
the databases from scratch, including the master database.
> Thanks in Advance for any advice.|||Provided all the path namews for location of master, model, tempdb etc =
and the user databses are the same, same service pack levels etc then I =
be;leive this should be OK. I would still test it though.=20
Being paranoid I would take at least 2 tape copies. And if possible =
restore to new disks BEFORE ditching the old ones.
But do you really ned to reinstall win2k to make the disk changes.?
Mike John
"Greg H" <anonymous@.discussions.microsoft.com> wrote in message =
news:F5F90AF5-EA63-4192-BEB5-E4238C2A624F@.microsoft.com...
> With the initial system DISK configuration now running out of space, =
it will be necessary to reconfigure the disk structure - with a reload =
of Win2k and SQL 2000. =20
> Question: Prior to the server rebuild, if SQL is stopped and =
system/user database device files are copied to tape, after the rebuild =
of the server (same name) and a reload of SQL 2000 to same drive, would =
it possible to stop SQL and copy the same database device files from =
tape to avoid a SQL restore of the databases. This would save some time =
and effort in recreating the databases from scratch, including the =
master database.
>=20
> Thanks in Advance for any advice.