Database Not shrinking

[stanlyn]

Hi,

I have a database made up of a single Primary file group that SHOULD be about 12GB in size. I added 2 columns, Image_Sla and Image_Tif each being of type varbinary(max). Next I added data to them and the database has grown to more than 250GB and is completely unmanageable. If I do a simple select it takes 10 minutes for results.

So, to fix I thought that I should move those 2 fields into their own filegroup along with a key. I created filegroups FG_ImageSLA and FG_ImageTIF and created 2 tables ImageSLA and ImageTIF and placed each one into their own matching file group. Next, I copied the data from the primary filegroup's table into their matching table located in the new filegroups.

Next I deleted the 2 columns from the main table located in the Primary filegroup.

The size of the Primary mdf and ldf files refuses to shrink....

I set the recovery type to simple and that did reclaim the space that the log file was using, however I cannot get the mdf to go any lower than 250GB, even after:
1. defragmenting the harddrive,
2. backing up the trans log,
3. doing a full master backup,
4. doing a shrink database with full release option set,
5. doing a shrink on all the files with full release option set,
6.

Each of these steps has been done many different times and also in different sequences.

What do I do next?

Thanks, Stanley



My frustration level is currently high with SQL Server... Sorry, I come from a VFP background where things are done predictable and quickly. Add a blob field and add data to it the table size increases, delete the field and the table size decreases instantly back to its previous size. So far, it feels like the transition from VFP to SQL is actually going backwards. I see why 1 person can easily manage 20 VFP sites, while he/she has their hands full managing only a few SQL sites.

 

[SQLScholar]

Next, I copied the data from the primary filegroup's table into their matching table located in the new filegroups.
[/qoute]

Did you copy the data or move the data?

If you copied it then it will still exist in the primary file group. I know this is probably mis-wording, but i thought it was worth a double check.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[stanlyn]

Hi Dan,

I done an insert from the original source table and afterwards I deleted the image fields.

Either way, why would the data still be in the Primary group after I deleted the fields?

Thanks, stanley

 

[SQLScholar]

Ok - sorry i missed that bit.

Couple of other things:

When you said:

"doing a full master backup"

Did you mean a full backup of the database the data is in or a full backup of the system database called master. If you havent done a full backup of the database then do that and try shrinking again.

If not this is what i would do (keep in mind backup etcs when you do this):

Set database from full backup model to simple.
Backup the database, backup the log, shrink database, shrink files.
Set back to full again.

Does that work?

Dan


----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

Here's what I think may have happened...

When you added the 2 additional columns, both varbinary(max), it needed to grow the size of the table. At this point, it probably didn't make much difference within the database. Then, you loaded data in to those columns. At this point, it caused your DB to grow a lot, particularly the table that the columns were added to.

SQL Server stores data on the disk in 8K data pages. If you have a table with just a couple int columns, then you will fit a lot of table rows in each 8K page. If you have a wide table, you may only be able to fit 1 table row per 8K page.

When you drop a column, it does not recover the space in the table or on disk. This is because growing and shrinking the size of a database is a relatively slow operation. Under normal circumstances, you shouldn't be shrinking your database. Also, you should be managing the "grow" process manually. This means that you should configure SQL Server to Auto-Grow, but you should also be monitoring the free space in the database and cause the file to grow manually (when it is convenient for you) instead of allowing SQL Server to grow the files when it may be inconvenient.

Anyway... I suspect the problem you are having is that your indexes are severely fragmented at the moment. This happens because you dropped to large columns from the DB. I suspect that reindexing the table will allow you to shrink the DB.

To recreate the indexes for a particular table....

DBCC dbReindex('YourTableNameHere')

After running the code above, try shrinking again.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom