1

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 '17 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 '17 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 '17 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 '17 at 17:15
0

Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.

  • 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. – HugoRune Mar 6 '17 at 12:29

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.