Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way.
Purging 'Local Versions' from Azure DevOps Server
Posted on November 3, 2020 | 2 minute read
Note: Use this at your own risk. As is best practice: Take backups and test it out in advance. This guidance may not be officially supported although it leverages existing APIs for Azure DevOps Server.
Recently I’ve had to work on a project to reduce the size of the tbl_LocalVersion table in Azure DevOps Server. This table stores metadata about Local Workspaces, Shelvesets and Server Workspaces. One way we can trim this down is by using the TFSConfig command line utility’s purgelocalversions job. This job is not documented but will essentially trim the size down on your tbl_LocalVersion table by providing you a mechanism to set a rolling window of Local Version records based on the date in which they were “Last Accessed”.
Enabling the command triggers the purge to begin immediately, and then schedules a job to run on Saturdays at 04:00 “Server Time”.
This should help keep the size of the tbl_LocalVersion down as it removes old workspaces.
The command is as follows:
Getting a glimpse of what’s being purged
If you are like me and are concerned about what exactly is going to be purged, we can take a look at the stored procedure that drives this operation, [dbo].prc_PurgeLocalVersions. You will notice that the mechanism used to identify rows for purging based on LastAccessDate. If we take this as a baseline we can build a stored procedure that allows us to view what Local Version records will be purged when the job next runs.
Take a look at the below [dbo].prc_PurgeLocalVersionsDryRun which can provide you a summary of what is slated to be purged.
Note: This stored procedure itself is not supported. It will work and has been tested with Azure DevOps Server 2019 and likely will continue to work. This of course can change with subsequent versions. Your mileage may vary.