I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).

In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.

Can I somehow generate text output in the stored procedure and have it included in the log?

It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.

  • SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
    – RDFozz
    Mar 6, 2017 at 15:21
  • I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
    – HugoRune
    Mar 6, 2017 at 16:51
  • But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
    – HugoRune
    Mar 6, 2017 at 16:52
  • Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
    – RDFozz
    Mar 6, 2017 at 17:15


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.