SSRS How to delete history snapshots older than X days programmatically?

Richard Uchytil 16 Reputation points
2023-08-30T23:06:13.99+00:00

We have a lot of SSRS reports creating a lot of history snapshots. We want to delete all snapshots for a report older than X days. Each report can have a different history retention policy, one might be to keep 30 days, one might be to keep 2 years. We'll handle this by putting this information in a sql server table and use that when we delete history snapshots.

On SwaggerHub I found a Delete HistorySnapshots, but it appears it's deleting ALL history snapshots, which we don't want. I can't find anything with ReportingServicesTools for deleting history snapshots.

One thought we had was to do the same thing that happens when you delete a history snapshot through the web interface. When you do this, what is really happening?

User's image

Is it running the only stored procedure DeleteHistoryRecord, or doing some other things too?

We know we can do it via SQL, but we're not sure of all the tables involved and don't want to leave any data behind, or mess things up.

Any thoughts or suggestions on the best way to do this?

Thanks!

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,796 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,039 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Limitless Technology 43,926 Reputation points
    2023-09-01T08:12:27.0266667+00:00
    Hello there,
    
    Try the command below, the sample delete snapshots older then 10 days.
    
    rg = 'snapshots'
    $snapshotnames = (Get-AzureRmSnapshot -ResourceGroupName $rg).name
    
    foreach($snapname in $snapshotnames)
    {
        Get-AzureRmSnapshot -ResourceGroupName $rg -SnapshotName $snapname | ?{($_.TimeCreated) -lt ([datetime]::UtcNow.AddDays(-10))} | remove-azurermsnapshot -force
    }
    
    Hope this resolves your Query !!
    
    --If the reply is helpful, please Upvote and Accept it as an answer--