SharePoint Content DB Shrink


I have a very large content (not log!) sharepoint database. About 80 Gb of its size is a dead, deleted from sharepoint data. I want to get rid of it, but common shrinking allows me to redeem only 1 Gb.

I dont know why sharepoint didnt mark this deleted data as shrinkable. It just seems like this sharepoint procedure is far from state of art.

So maybe you - sql pros - know some magical script that i can cast on my db to unravel this knot ? )

This should be a common trouble, but i cant find any solution in my realm (

September 9th, 2010 4:01pm

Hi evernite,

Sharepoint content databases are very sensitive to DDL statements, I wouldn't mess too much with them.

Do you know for sure that the data is deleted, and not in the recycle bin or something?


There is an excellent document available from Microsoft about Database Maintenance for Sharepoint: http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2010 5:32pm

Hi ,

Actually i got almost the same problem, the content database was arround 200 GB, so in order to reduce it i  deleted two sub-sites (70 GB) , but when i checked the Content database size i found it the same as before deleting these content !

I checked the recycle bin but it was emply, any one has an explaination ?

Many thanks

September 15th, 2010 4:29pm

Have you checked the size on the filesystem or the space used in SQL (you can do this by executing sp_spaceused against the content db)?

SQL Server does not automatically shrink files when data in the files disappears.

Tom

Free Windows Admin Tool Kit Click here and download it now
September 15th, 2010 5:12pm

Sorry for late reply.

I dont know what happened but after a week my db redeemed space at last ! I have just shrinked over 80 Gb of space.

I urge to know though what time job process in sharepoint or some sql job did that to be able to launch it manually!

Did you have the same experience with your 200 Gb base ? )
September 22nd, 2010 5:19pm

evernite,

I would bet that you data sat in the SiteCollection Recycle Bin (Site Action -> Site Settings -> recycle bin under Site Collection Administration), which has two tiers so that both users and administrators have an opportunity to restore deleted content before it is permanently deleted.

The reason I feel fairly confident is because each tier of the Site Collection Recycle bin has a time limit.  After deleted content has been in the recycle bin for that set timeframe it is deleted.  So if a user deletes an item it will remain in the 'End User Recycle Bin' for x days, after which it is automatically moved to the 'Deleted From End User Recycle Bin' where it remains for another y days.  Only after being in the 'Deleted From End User Recycle Bin' section for y days if the item permanently deleted and the space returned to the content database.

I cant recall how to set x and y but i'm sure you could find out by searchin (Bing of course)

Free Windows Admin Tool Kit Click here and download it now
February 24th, 2012 6:49am

evernite,

I would bet that you data sat in the SiteCollection Recycle Bin (Site Action -> Site Settings -> recycle bin under Site Collection Administration), which has two tiers so that both users and administrators have an opportunity to restore deleted content before it is permanently deleted.

The reason I feel fairly confident is because each tier of the Site Collection Recycle bin has a time limit.  After deleted content has been in the recycle bin for that set timeframe it is deleted.  So if a user deletes an item it will remain in the 'End User Recycle Bin' for x days, after which it is automatically moved to the 'Deleted From End User Recycle Bin' where it remains for another y days.  Only after being in the 'Deleted From End User Recycle Bin' section for y days if the item permanently deleted and the space returned to the content database.

I cant recall how to set x and y but i'm sure you could find out by searchin (Bing of course)

That or Ghost Cleanup finally caught up to the heavy amount of deletes and actually deallocated the ghosted records inside of the database allowing the space to be reclaimed.  When data is deleted in SQL Server it isn't actually deleted, it is instead marked as being ghosted to allow a background process to come by and clean up the ghosted records in the database, making it much faster to the DELETE operation, as well as to any rollback operation.

Reference:

http://sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(15-of-31)-Tracking-Ghost-Cleanup.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx

http://sqlskills.com/BLOGS/PAUL/post/Ghost-cleanup-redux.as

February 24th, 2012 10:09am

Hi,

if it is SQL 2008 R2. try to shrink twice. 

thx 

Free Windows Admin Tool Kit Click here and download it now
April 12th, 2012 10:07am

Use the following script to shrink the database in chunks.you need to specify only the logical filename and size to shrink. It will shrink all free space.

I have already highlighted th in the script which need to be changed.

-- Set Name of Database file to shrink

-- Set Increment to shrink file by in MB

declare @DBFileName sysname
 
declare @TargetFreeMB int
 
declare @ShrinkIncrementMB int
 
-- Set Name of Database file to shrink
 
set @DBFileName = '<Logical filename  which need to be shrunk>'
 
-- Set Desired file free space in MB after shrink
 
set @TargetFreeMB = 1000 --need to change the size
 
-- Set Increment to shrink file by in MB
 
set @ShrinkIncrementMB = 1000
 
-- Show Size, Space Used, Unused Space, and Name of all database files
 
select
 
[FileSizeMB] =
 
convert(numeric(10,2),round(a.size/128.,2)),
 
[UsedSpaceMB] =
 
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
 
[UnusedSpaceMB] =
 
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
 
[DBFileName] = a.name
 
from
 
sysfiles a
 
declare @sql varchar(8000)
 
declare @SizeMB int
 
declare @UsedMB int
 
-- Get current file size in MB
 
select @SizeMB = size/128. from sysfiles where name = @DBFileName
 
--Print '@sizemb = '+ @sizemb
 
-- Get current space used in MB
 
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
 
--print '@usedMB = ' + @usedmb
 
select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [TargetFreeMB] = @TargetFreeMB,[Total] =@UsedMB+@TargetFreeMB ,[DBFileName] = @DBFileName
 
-- Loop until file at desired size
 
while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
 
begin
 
set @sql =
 
'dbcc shrinkfile ( '+@DBFileName+', '+
 
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '
 
print 'Start ' + @sql
 
print 'at '+convert(varchar(30),getdate(),121)
 
exec ( @sql )
 
print 'Done ' + @sql
 
print 'at '+convert(varchar(30),getdate(),121)
 
-- Get current file size in MB
 
select @SizeMB = size/128. from sysfiles where name = @DBFileName
 
-- Get current space used in MB
 
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
 
select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName
 
end
 
select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName
 
-- Show Size, Space Used, Unused Space, and Name of all database files
 
select
 
[FileSizeMB] =
 
convert(numeric(10,2),round(a.size/128.,2)),
 
[UsedSpaceMB] =
 
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
 
[UnusedSpaceMB] =
 
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
 
[DBFileName] = a.name
 
from
 
sysfiles a

April 12th, 2012 3:14pm

I sugest you remove the files, run a sharepoint backup and restore it to a new fresh web application. Then you can detach, attach the database to the original web app if you like to. See more here:

http://sharepointkaos.wordpress.com/2013/08/15/reduce-the-size-of-the-sharepoint-content-database-without-shrinking-it/

Free Windows Admin Tool Kit Click here and download it now
August 15th, 2013 4:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics