[RESOLVED] Shrink DB (SQL Server 2005)

[GaryMazzone]

I have the following senerio...

I backup up a database and restored it to a new name. Ran some SQL against this restored database to drop all but one table. And all but 3 columns from the remaining table. When first restored the DB size was 59 Gig. After dropping and doing a DBCC ShrinkDatabase the size went to 34 Gig.
The column that was left was and Identity (int), GUID (uniqueidentifier) and a document (Varbinary).

In the original database I drop the document column for the same table. After doing a DBCC Shrink Database there is no change in DB size. There appears to be approx 4% free space in the database after the column drop. How do I go about reclaiming space after the drop.

I would think that I should be able to reclaim at least 30 Gig of space.

Thanks
Gary

[Pradeep1210]

You can truncate the log files too if they are no use to you. They too take a lot of space.
I think your missing 30 Gig is in log files. Have you checked the log file size?

e.g.
Dbcc shrinkfile('templog')

[GaryMazzone]

No in the log..... Already di that. I'm looking at the data file size.

[brucevde]

ShrinkDatabase will not shrink a database file less than its minimum size. The minimum size is set by Create Database or Alter Database Modify File statements. Also, I think the Database option "Automatically Grow File" changes/sets the minimum size as well.

The returned result set of ShrinkDatabase should help in determining how large of a file is required.

You might want to try DBCC ShrinkFile instead.

[GaryMazzone]

Bruce

I'm already doing that (I'll post the code I'm using later on my home system now). The bigger question I'm trying to answer is the issue of the free space. When I drop the column from the table there is now difference in the amount of free space in the database. Is it because the rows are still spread around the datapages? Would I need to re-orginize the table to get the space back?

I hope I'm making sense.... Do I still have rows spread accros multiple datapages because they were spread due to the size of the data in that column I'm droping. It was a created as an Image datatype. In the database that this information is moving to after useing ShrinkFile I get the datafile size down to 34 gig from 57 gig. I can't get the same reducion on the orginial database.

[GaryMazzone]

Back on work machine this the the script I'm using for shrink:

DECLARE @DataFileFreeSpaceGB INT
DECLARE @LogFileFreeSpaceGB INT
 
SELECT @DataFileFreeSpaceGB = 10 
SELECT @LogFileFreeSpaceGB = 1 
 
 
DECLARE @DBName  VARCHAR(100)  --Database Name
DECLARE @filenameD VARCHAR(500) -- DataFile
DECLARE @filenameL VARCHAR(500)  -- Logfile
DECLARE @DSpaceUsed DECIMAL(10) --Spaceused in Meg
DECLARE @LSpaceUsed DECIMAL(10) --Spaceused in Meng
DECLARE @sStr VARCHAR(200)  -- A string SQL statement to EXECute
 
SELECT @DBName = DB_NAME()
SET @sStr = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY Simple'
EXEC (@sStr)
 
--DataFile
SELECT @fileNameD = name From sys.database_files Where type = 0
SELECT @DSpaceUsed = (FILEPROPERTY (@filenameD,'SpaceUsed ') * 8192.0)/1024/1024
SET @DSpaceUsed = @DSpaceUsed + (@DataFileFreeSpaceGB * 1000)
SET @sStr = 'DBCC ShrinkFile (' + @fileNameD + ',' + Convert(VARCHAR(50),@DSpaceUsed) + ')'
EXEC (@sStr)
 
--Logfile
SELECT @fileNameL = name From sys.database_files Where type = 1
SELECT @LSpaceUsed = (FILEPROPERTY (@filenameL,'SpaceUsed ') * 8192.0)/1024/1024
SET @LSpaceUsed = @LSpaceUsed + (@LogFileFreeSpaceGB * 1000)
SET @sStr = 'DBCC ShrinkFile (' + @fileNameL + ',' + Convert(VARCHAR(50),@LSpaceUsed) + ')'
EXEC (@sStr)
 
SET @sStr = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
EXEC (@sStr)

[GaryMazzone]

Found the answer in Books On-Line. I need to Rebuild the Primary Key index. Once I did that I was able to use the script above to reclaim the space.