The production DB is on a SQL Server 2000 box, but all this was done on a backup that I restored to a SQL Server 2005 box.
I have a table that stores images (we'll call it img_table) that shows this when I run sp_spaceused:
Reserved: 105.7GB
Data: 8.4GB
Unused: 97.3GB
Index_Size: 40KB
Right now the DB's total size is 130GB. If I could trim that down to 33 by cleaning up this table it might provide a nice performance increase or at the very least make maintenance less of a headache.
I spent several hours looking around Google and this site for a way to bring this down to a more manageable size. I found a ton of different suggestions but none of them worked. Here's what I've tried so far: (syntax might be a bit off, but I did eventually get all these to go through without errors)
I ran sp_help and noted that there was a clustered index for the table called PK_img_table since the table has a primary key.
I ran DBCC SHOWCONTIG (img_table,PK_img_table) and saw a lot of logical fragmentation in the index.
I ran DBCC INDEXDEFRAG (DBName, 'img_table', 'PK_img_table') and the index size went down to 40k from 800k, but the unused was pretty much unchanged. DBCC SHOWCONTIG shows 0% logical fragmentation now.
So I ran DBCC DBREINDEX ('img_table', 'PK_img_table') and not much happened. I think the reserved, data, and unused went up or down by a few KB each.
Next I ran DBCC CLEANTABLE ('DBName', 'img_table') and again not much happened.
I tried dropping the index and recreating it, then going through all the above steps, but there's still 97GB of unused space.
I tried running a SHRINK command on the DB but the log file quickly filled up the disk. I've thought about trying it again with recovery model on simple but haven't yet.
I thought about trying SELECT * INTO img_table2 FROM img_table and seeing if the copied table was smaller but I don't have enough free space on my drive for it.
Does anybody have any ideas, or is there a valid reason it should be acting like this?
Thanks in advance!
Start Free Trial
