Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Shrinking database after dropping columns

Shrinking database after dropping columns

Hello all. Someone might have asked this already but I can't find a related thread.

My problem is a DB that is a little over 500Gb in size. It did not be to be in Full Recovery mode so I changed it to Single Recovery mode and reclaimed most of the space the log file was using.

Now, I dropped three BLOB column from a table and I need that space to be available to the OS. So far I have tried CLEANTABLE, reindexing and shrinking the files. Still no reduction if data file size. Shrinking ran for hours last night. Currently I am reducing the Initial DB Size and it's been running for the past hour.

My question to you is: WHAT IS THE MOST EFFECTIVE WAY OF RECOVERING SPACE AFTER DROPPING COLUMNS FROM A TABLE?. I would very much appreciate if you could provide tips for each recovery model.

Thank you in advance!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

RE: Shrinking database after dropping columns

First thing you need to do is confirm that there is space to recover:


select fileproerty(name, 'SpaceUsed')/128, size/128
from sys.database_files 
If there is a significant amount of space available, you can do a few things.
  1. Just go ahead with DBCC SHRINKFILE
  2. Try to identify tables with pages at the "end" of the file, and either export them, or move them to another filegroup.
I find with shrinkfile, it is best to break up the shrink into a number of chunks (say 5 GB shrinks). Shrinking by the full amount at once may be faster, but you gt no feedback from it. Shrinking in 5 GB chunks will give you a sense that it is working. Also, once you shrink a 5 GB chunk, that can't be rolled back, unless someone is pumping data into the database behind your back.

RE: Shrinking database after dropping columns

Thanks yelworcnm.

I don't know how to identify tables with pages at the end of the file. "End"? What's implied? The columns I dropped occupied the most space and moving them to another file group is out of the question as I only have one data drive and therefore would still have the space issue. I did check that there was available space:

CODE -->

select name, size/128.0, 
fileproperty(name, 'SpaceUsed')/128.0, size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB 
from sys.database_files; 

I did shrink the files from 500Gb to 300Gb from SSMS; it took at least three hours!

Thanks a lot.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close