Home / server / Questions / 143160
Accepted
Mike
Asked: 2010-05-19 13:43:46 +0800 CST

Emptying site collection recycle bin doesn’t make content DB smaller?

  • 772

I deleted everything from a site collection recycle bin and remoted into the SQL server the content database is located on, went to view the WSS_Content and the sucker didn't get smaller. I had about a good 2 or 3 gigs of folders with files in the recycle bin. I just want to make sure that it is getting deleted.

Is there something I am missing? Or does the SQL server not update file sizes properly?

MOSS2007 IIS6 WinSer2003

windows sql-server sharepoint database

2 Answers

  1. Best Answer

    Databases don't automatically shrink, just because there is less in them. Resizing the database on the fly is a performance nightmare, so most people take great pains to be sure that the file is big enough that they'll be able to grow it during off-peak hours.

    You need to run DBCC SHRINK DATABASE

    • 4

  2. The content DB should not shrink. You can run DBCC shrink database if required but I would not get in to that habit. I would run this script prior to shrinking to see if you have "excessive" free space

    SELECT DB_NAME(database_id) AS DatabaseName, 
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH' 
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,
SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id

    Source for this code is here

    • 1