maintenance-planssql serversql-server-2012
I have a Maintenance plan that I wish to execute nightly. The purpose is to set an archived bit against records that are more than 60 days old. This bit all works fine, however I'd like to log (and email an operator) about how many records were affected.
I have a stored proc that does all the heavy lifting, and it is defined in an execute T-SQL task with an out parameter that contains the number of records affected.)
So how do I pass that value to a notify task, or log it?
sp_executesql is for executing ad-hoc T-SQL. So you should try:
EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
This doesn't address your primary question but I can't help but propose a different approach than the one you're currently taking. You should be backing up the transaction log frequently enough that, in the event of a disaster, you won't lose any more data than your SLA allows. If you can afford to lose up to 30 minutes of data, then you should probably be backing up the transaction log every 15 minutes. You can certainly do this through the maintenance plan wizard (including picking which databases to run on, and one option is definitely "All user databases"):
Your current approach of switching to simple, running a shrinkfile, then switching back to full, is quite self-defeating. You're in full recovery model but you're not getting any of the benefits from it. Backing up the log throughout the day will keep your t-log size down as backing up the log allows space within the file to be re-used. If you don't need point-in-time recovery, then switch to simple recovery. This allows the space within the log file to be re-used automatically. In either case you shouldn't have to manually shrink the file anymore - which is very expensive process to repeat every night (never mind one that impacts users every time it auto-grows during the day).
Best Answer
Using an SSIS expression on the Notify Operator task properties for the message subject or body, you can construct whatever you want from variables written earlier.