Shrinking database after dropping columns 1

[TheBugSlayer]

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).

 

[yelworcm]

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.
[ol 1]
[li]Just go ahead with DBCC SHRINKFILE[/li]
[li]Try to identify tables with pages at the "end" of the file, and either export them, or move them to another filegroup.[/li]
[/ol]

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.

 

[TheBugSlayer]

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:

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).