Reducing database size after dropping text column

[Thomas]

Hello,

A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.

I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.

Does anyone know what I should run?

Thanks,
Tom

[Kevin3NF]

Are you talking about DBCC Shrinkdatabase?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

"Thomas" <th***********@data.iatn.net> wrote in message
news:4f**************************@posting.google.c om...

Hello,

A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.

I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.

Does anyone know what I should run?

Thanks,
Tom

[Andrew J. Kelly]

I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.

--

Andrew J. Kelly
SQL Server MVP
"Thomas" <th***********@data.iatn.net> wrote in message
news:4f**************************@posting.google.c om...

Hello,

A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.

I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.

Does anyone know what I should run?

Thanks,
Tom

[Thomas]

"Andrew J. Kelly" <sq************@shadhawk.com> wrote in message news:<#C**************@TK2MSFTNGP12.phx.gbl>...

I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.

Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?

Thanks,
Tom

[Andrew J. Kelly]

Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.

--

Andrew J. Kelly
SQL Server MVP
"Thomas" <th***********@data.iatn.net> wrote in message
news:4f*************************@posting.google.co m...

"Andrew J. Kelly" <sq************@shadhawk.com> wrote in message

news:<#C**************@TK2MSFTNGP12.phx.gbl>...

I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.

Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?

Thanks,
Tom

[Thomas]

"Andrew J. Kelly" <sq************@shadhawk.com> wrote in message news:<#I**************@TK2MSFTNGP12.phx.gbl>...

Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.

Hi Andrew,

Thanks for the help on this, it's quite bizarre.

There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)

I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.

At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.

Thanks,
Thomas

[Andrew J. Kelly]

One other point that may be a factor here. You can only shrink the db and
log file down to the size it was originally created at and no more. So if
the db was created at 3GB you can run shrink all you want and nothing will
happen. Does Shrinkfile show any estimated pages that can be removed?

--

Andrew J. Kelly
SQL Server MVP
"Thomas" <th***********@data.iatn.net> wrote in message
news:4f**************************@posting.google.c om...

"Andrew J. Kelly" <sq************@shadhawk.com> wrote in message

news:<#I**************@TK2MSFTNGP12.phx.gbl>...

Do you know for sure there is enough free space to effectively shrink the db? Is SQL Server still reporting the size of that table to be what it was before you dropped the columns? Do you have a clustered index on the table? IF so you might want to do a DBREINDEX and see if that helps any.

Hi Andrew,

Thanks for the help on this, it's quite bizarre.

There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)

I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.

At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.

Thanks,
Thomas