Do you ever look at your Recovery Solution job queue and wonder "I wonder how far along that Integrity Check/SSM job is?" Well I did, and was very frustrated since there wasn't a good way I had found to monitor the progress. Well, by using the following Stored Procedure, you too can find out about the progress of the job.
To install this, just load the attached .SQL file into Query Analyzer (or whatever tool is used in SQL 2005), select your Recovery Solution database, and execute it. It will add the spGetRSJobStatus Stored Procedure (SP) to the database. To run the query, just execute the SP from Query Analyzer:
EXEC spGetRSJobStatus
Note that this works best for checking the Integrity Check job's status as it (by default) scans all BLOB files; maybe later I will update this to account for the use of the "IntegrityStartDiskFileKey" and "IntegrityEndDiskFileKey" per AKB 25398. To check the progress of SSM's Compaction phase, see AKB 41182, or for another view of SSM progress check AKB 41309.