SQL Server’s 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.

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!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.