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?

  • Which parts of the maintenance plan are you implementing? Some of them are far less than optimal and writing your own script to do it will make much more sense. I've written a replacement for the unreliable, unsupported and undocumented sp_MSforeachdb: mssqltips.com/sqlservertip/2201/… – Aaron Bertrand Sep 4 '12 at 15:24
  • We need to truncate our transaction logs nightly after doing a full db backup. We have a script that does the SET TO SIMPLE; SHRINKFILE; SET TO FULL, but I want to be able to code it to all user databases, not hardcoded, so that when we add new databases, we don't need to manually add them to the script. – eidylon Sep 4 '12 at 15:26
  • Why are you truncating the transaction logs? Are you in full recovery model but not running any transaction log backups? – Aaron Bertrand Sep 4 '12 at 15:27
  • I don't see any task in the maintenance plans for transaction log backups. The Backup task just seems to do the db files. – eidylon Sep 4 '12 at 15:29
  • If you perform regular transaction log backups, you shouldn't need to perform any switches from simple to full and you'll keep a healthier transaction log chain and the ability to restore to a point in time. Currently you may as well just stay in simple. – Aaron Bertrand Sep 4 '12 at 15:30

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"):

enter image description here

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).

  • Ohh, I just found it in the designer; Edit... on the Backup Database Task has a Backup Type dropdown which has as the last item "Transaction Log". This should do that then, yes? – eidylon Sep 4 '12 at 15:39
  • Yes, you'll want to back up the t-log throughout the day. – Aaron Bertrand Sep 4 '12 at 15:41
  • Thanks so much! One more question... will the "History Cleanup Task" that cleans up old backup history also clean the old translog histories? – eidylon Sep 4 '12 at 15:46
  • Yes, it should handle both. – Aaron Bertrand Sep 4 '12 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.