473,372 Members | 1,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Reducing database size after dropping text column

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
Jul 20 '05 #1
6 4375
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

Jul 20 '05 #2
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

Jul 20 '05 #3
"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
Jul 20 '05 #4
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

Jul 20 '05 #5
"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
Jul 20 '05 #6
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

Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: lkrubner | last post by:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR, INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql? PostGre info would also be useful. I'm trying to...
1
by: Beyonder | last post by:
I'm using InnoDB with Mysql and I've used delete to delete over 10 million records, but the InnoDB database is still the same size on the disk (over 100 gigs) is there a way to get Mysql/InnoDB to...
3
by: Chris H | last post by:
Ok in the following code I am trying to have my keywards meta tag auto insert the top 10 titles from most recently added database entries, right now the while loop is only setting the $keyword...
4
by: Patrick | last post by:
I have a tablespace that contains the LOB data for 3 tables which exist in other tablespaces. Even after reorging the LOB tablespace with the LONG option, the high water mark is still too high. I...
10
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the...
8
by: Jeff | last post by:
I have a db that has a couple of times closed Access completely when Saving work. So I usually compact and decompile and this seems to fix the problem. But not his time. It has come back again....
1
by: Rameel | last post by:
Friends, I'm probably being more critical with VB.Net Windows application. I have Developed VisualStudio 20005 VB.Net Windows application how willl i be able to save a specific record into my...
1
by: Muchach | last post by:
Hello, Ok so what I've got going on is a form that is populated by pulling info from database then using php do{} to create elements in form. I have a text box in each table row for the user to...
1
by: Brit | last post by:
I have an ASP file that retrieves names from an Access database for 4 different categories of membership, which the visitor to the page selects (corporate, institutional, regular, or student). The...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.