Mass Deleting Document Version History


Version history takes a huge amount of disk space
The following SQL will delete the version history for a specific doc or set of docs
BE VERY CAREFUL! Remove the first line and run a SELECT statement first so you don’t delete the wrong versions!

Set the DirName = to the path to the doc library
Set the leafname = to the name of the file

delete from alldocversions
from alldocversions v inner join alldocstreams s on s.id = v.id
inner join alldocs d on d.id = s.id
where DirName = ‘FolderPath’ and leafname like ‘a%’

Here’s a cursor procedure to delete selected files

SET NOCOUNT ON
declare @dirname varchar(100)
declare @leafname varchar(255)

DECLARE complex_cursor CURSOR FOR
    select distinct DirName, LeafName
 from alldocversions v inner join alldocstreams s on s.id = v.id
 inner join alldocs d on d.id = s.id
 where DirName like ‘Folderpath%’ and leafname like ‘c%’;
OPEN complex_cursor;
FETCH NEXT FROM complex_cursor INTO @dirname, @leafname
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT ‘Deleting ‘ + @dirname + ‘, ‘ + @leafname

 DELETE FROM alldocversions
 FROM alldocversions v inner join alldocstreams s on s.id = v.id
 inner join alldocs d on d.id = s.id
 where DirName = @dirname and leafname = @leafname

 FETCH NEXT FROM complex_cursor INTO @dirname, @leafname
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

SET NOCOUNT OFF

Advertisements

3 Responses to “Mass Deleting Document Version History”

  1. Callum Says:

    You shouldn’t ever be directly accessing the SharePoint databases, its not supported by Microsoft and can result in unwanted locks and other errors.
    To do things like this you need to learn to use the SharePoint APIs that exist for exactly these purposes.

  2. carpet in tampa Says:

    Way cool! Some very valid points! I appreciate yoou penning this post and the rest
    of thhe site iss really good.

  3. Lettie Says:

    Consequently the business provides the most effecctive Roolex watches
    to its potential customers probably the most fabulous worldwide.
    The Rolex watches in India have thheir products like the Oyster Perpetual watches,
    Professional watches, Cellini watches etc. That way yyou can monitor your progress
    toward short and long term goals.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: