Maintenance plan logging by Sql Server..

knt1N 286 Reputation points
2021-09-03T07:39:05.167+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,099 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,580 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 42,561 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.


1 additional answer

Sort by: Most helpful
  1. YufeiShao-msft 7,076 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.
    129054-931.png

    You can refer to:
    https://www.mssqltips.com/sqlservertip/3225/sql-server-maintenance-plans-reporting-and-logging/