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
