Maintenance plan logging by Sql Server..

knt1N 286

The DB server uses SQL Server 2017 Standard.

I would like to perform a batch process in the maintenance plan that periodically updates the registered data.
For example, a maintenance plan that performs bulk data updates at night while no one is accessing the DB.

I can see from the log set in "Reporting and Logging" whether this process was performed.
And I can see "Job Activity Monitor" to see if the job that the maintenance plan called was successfully completed.

However, if an error occurs, I want to check the log with more detailed information.
For example, include a SELECT statement in a job that contains a batch SQL statement.
And if the result of executing the SELECT statement is output in the log, what I want to do is realized.

Does those who know how to make this happen?
I'd appreciate it if someone could tell me how to solve this problem.

Accepted answer

Olaf Helper 43,246 Reputation points

2021-09-03T08:47:14.887+00:00

First, if you want to run plain T-SQL statements, then don't use a maintenance plan, use a simple SQL Server-Agent job with a a simple T-SQL job step instead.

I like to suggest to implement all logic in a stored procedure, which also log all performed steps into a user table.
In the job you can define to send an email to you in case of an error.
Please sign in to rate this answer.
knt1N 286 Reputation points

2021-09-09T04:35:00.14+00:00

Hello.
Thank you for your answer.

Certainly, it seemed possible if you were to log logs in a user table instead of outputting them to a file.

By the way, I also created the process of executing a plain T-SQL statement with SQL Server-Agent job, Just like your suggestions.
However, SQL Server-Agent job is not scheduled, and this job kicks in from the maintenance plan. The schedule is set up in the maintenance plan.
This is because there are other scheduled operations besides plain T-SQL statements, and I wanted to unify the scheduled processing to the maintenance plan as much as possible.
Other scheduled processes include, for example, "Rebuild Index" in a maintenance plan.

Thank you again for your advice.

Olaf Helper 43,246 Reputation points

2021-09-09T05:06:45.613+00:00

However, SQL Server-Agent job is not scheduled, and this job kicks in from the maintenance plan. The schedule is set up in the maintenance plan.

And a scheduling in a maintenance plan creates a SQL Server-Agent job; no difference if it's created by a MP or manually.

knt1N 286 Reputation points

2021-09-09T06:28:05.547+00:00

I also did some validation on this. Certainly, a SQLServer-Agent job named "[maintenance plan name].Subplan_1" is automatically generated from the maintenance plan.

Initially, we planned to edit this "[maintenance plan name].Subplan_1" to implement a plain T-SQL statement.
However, for some reason, when I did that, the maintenance plan no more output the log file specified by "Reporting and Logging".
Since the log file was output again after this job was re-created, I did not edit the job that the maintenance plan automatically generated, and it took a way to call other jobs that were manually created.
Sign in to comment

1 additional answer

YufeiShao-msft 7,091 Reputation points

2021-09-03T09:00:45.56+00:00

Hi @knt1N ，

Perhaps what you need is extended information for the logs, if this option is turned on it will be possible to include more information in the logs, showing all the T-SQL code that was run.

You can refer to:
https://www.mssqltips.com/sqlservertip/3225/sql-server-maintenance-plans-reporting-and-logging/
Please sign in to rate this answer.
knt1N 286 Reputation points

2021-09-09T04:54:27+00:00

Hello.
Thank you for your answer.

Unfortunately, the result of the SELECT statement was not written to the log even if "Log extended infomation" was ON.
As a difference in the log contents when changing from OFF to ON, the following sentences have been added to the log.

Command:EXEC msdb.dbo.sp_start_job @job_id=N''ba713b9c...''

Since plain T-SQL statements could not be set directly in the maintenance plan, I set it to SQL Server-Agent job and kick it from the maintenance plan, but it seems that only the execution contents of the kick source are output.
This structure is due to the failure of the Maintenance Plan Wizard to write a plain T-SQL Statement. If there is a way to write directly, or to log the results of select statements for jobs that the maintenance plan kicked, I would like to know.
If this is not possible, I would like to think of a way to record other than log files.

Thank you again for your advice.
Sign in to comment

Share via

Maintenance plan logging by Sql Server..

1 additional answer