SQL Server with Mr. Denny

Feb 20 2013   2:00PM GMT

Cleaning Up Reporting Services Snapshots

Denny Cherry Denny Cherry Profile: Denny Cherry

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]
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

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.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: