I'm setting up a maintenance plan on Sql Server.
I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as you can with pretty much every other task type.
Is there a way to do this, perhaps with an "in-place" parameter in the script that would be used to substitute the database name?
I want to run a nightly script in the maintenance plan, but it needs to be able to put the database name in the script.
Should I call a stored procedure from the maintenance plan that iterates through all the databases desired using dynamic SQL to execute my statement?
Best Answer
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).