Binary & image columns and unused space

Question

Post reply

Binary & image columns and unused space

anandr

SSC Rookie

Points: 25
More actions
February 2, 2002 at 11:27 am

#104703

Hi
I have a table that has binary and image columns. sp_spaceused shows a lot of unused space. I tried BCP out/truncate/BCP in, but bcp in failed probably because of binary/iamge columns.
What is the best way to free up the unused space.
Anand

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Take a look at dbcc shrinkfile.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

saugup SSC Veteran Points: 283 More actions · Answer 2

I don't think that you are not able to shrink the file because of the binary data. Just use DBCC shrink and also make sure that you are doing some transaction as I have seen that DBCC shrink database does not shrink the databases untill you perform some transactions.

You can also try taking backups.

quote:
Hi
I have a table that has binary and image columns. sp_spaceused shows a lot of unused space. I tried BCP out/truncate/BCP in, but bcp in failed probably because of binary/iamge columns.
What is the best way to free up the unused space.
Anand

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

DBCC SHRINKDATABASE and DBCCSHRINKFILE will shring the data files, but may not necessarily shrink the log files, because the active portion of the log may be at the end of the log file.

Under SQL Server 2000, first a DBCC SHRINKFILE command will need to be run, and then if the log hasn't shrunk to the desired size, a BACKUP log file will need to be run to bring the active portion to the beginning. SQL Server will take care of the dummy transactions to do this. After that, another DBCC SHRINKFILE command will need to be run. Here's the Q article describing this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318

With SQL Server 7, the dummy transactions aren't done by SQL Server, so that has to be done by the DBA. Here's the Q article that explains this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650

Steve Jones has posted a script which creates a stored procedure to take care of the details. Here is the link to it:

http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Also you may want for sake of not causing undue growth again want to clean the indexes and update usage to have the database in order before you try shrinking. In SQL 7 use DBCC DBREINDEX but in SQL 2K you can use DBCC INDEXDEFRAG (but I would make sure you have SP1 minimum on SQL 2K, if not DBREINDEX does work). And DBCC UPDATEUSAGE. Then do a BACKUP LOG to get the log file in order, and finally shrink the DB. Doing this will give you the best performance. Note also with DBCC DBREINDEX it looks the data tables so do it on the off peak times for best results and fewer complaints.

meghardy Default port Points: 1453 More actions · Answer 5

Is this only for tempdb or should we do this on all databases. I have just implemented a job that does the dbcc dbreindex periodically. Would you advise doing dbcc updateusage after doing the reindex?

Thanks ....Meg

Antares686 SSC Guru Points: 125444 More actions · Answer 6

There is no need to do DBREINDEX on tempDb but I use it on all others (model can also be skipped). And yes I use DBCC UPDATEUSAGE after the reindex to make sure sysindexs is reporting properly.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)