Sunday, January 23, 2011

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

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

  • 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

  • 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

    From Jim B

0 comments:

Post a Comment