Cleaning Up Reporting Services Snapshots

Published On: 2013-02-20By:

If you’ve been running a SQL Server Reporting Services machine for a long time you may have noticed that your ReportServerTempDB database has filled up quite large over the years with lots of crap.  And it’s just kept going.  This is because sometimes SQL Server Reporting Services may be keeping more Snapshot data than it is supposed to (or maybe it has old stuff from before you limited how much crap it could keep.

In the case of one SSRS instance I ran across recently there were over 40k expired snapshots sitting in the ReportServerTempDB database.  Microsoft does include a stored procedure to get rid of these snapshots, but it only works on a single snapshot at a time, so you’ll need to call the stored procedure in a loop to clean up the extra crap.  This script worked nicely for me.

USE [ReportServer]
GO
DECLARE @return_value int,
@SnapshotsCleaned int = 1,
@ChunksCleaned int,
@TempSnapshotID uniqueidentifier
while @SnapshotsCleaned <> 0
EXEC @return_value = [dbo].[CleanBrokenSnapshots]
@Machine = @@SERVERNAME,
@SnapshotsCleaned = @SnapshotsCleaned OUTPUT,
@ChunksCleaned = @ChunksCleaned OUTPUT,
@TempSnapshotID = @TempSnapshotID OUTPUT
GO

You’ll notice that I’m simply setting the @SnapshotsCleaned value to 1 then running the procedure in a loop until that variable comes back as 0. That variable will only ever come back as 0, 1 or 2 (based on the SQL 2008 R2 version of Reporting Services) but the input parameter is INT so the variable matches that.

In any case, hopefully this helps you clean up your SQL Server Reporting Services ReportServerTempDB databases.

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via