Accelerated Database Recovery: Forcing Cleanup

Forcing Cleanup

There’s a stored procedure called sp_persistent_version_cleanup we can use to clean out PVS data.

There’s some helpful information about it in sp_helptext:

create procedure sys.sp_persistent_version_cleanup  
(  
 @dbname sysname = NULL,  -- name of the database.  
 @scanallpages BIT = NULL  -- whether to scan all pages in the database.  
)  
as  
begin  
 set @dbname = ISNULL(@dbname, DB_NAME())  
 set @scanallpages = ISNULL(@scanallpages, 0)  
  
 declare @returncode int  
  
 EXEC @returncode = sys.sp_persistent_version_cleanup_internal @dbname, @scanallpages  
  
 return @returncode  
end

We can pass in a database name, and if we want to scan all the pages during cleanup.

Unfortunately, those get passed to sp_persistent_version_cleanup_internal, which only throws an error with sp_helptext.

Locking?

While the proc runs, it generates a wait called PVS_CLEANUP_LOCK.

This doesn’t seem to actually lock the PVS so that other transactions can’t put data in there, though.

While it runs (and boy does it run for a while), I can successfully run other modifications that use PVS, and roll them back instantly.

If we look at the locks it’s taking out using sp_WhoIsActive…

sp_WhoIsActive @get_locks = 1;

<Database name="StackOverflow">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
</Database>

It doesn’t seem to do anything terribly invasive.

Better Than Yours

Watching the session with XE also doesn’t reveal locking, but it may be cleverly hidden away from us.

In all, it took about 21 minutes to cleanup the 37MB of data I had in there.

I don’t think this is my fault, either. It’s not like I’m using a clown shoes VM here.

Thanks for reading!



4 thoughts on “Accelerated Database Recovery: Forcing Cleanup

  1. Hi Eric,
    Do you know how long shrink database will take once a cleaner process cleaned up the ADR enabled database with unneeded versions? Or does the cleaner process shrinks the size as well?

Leave a Reply

Your email address will not be published. Required fields are marked *