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:
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.