SQL Server Maintenance Plans Maintenance plan

Server
Maintenance plans that are automatically performed on a regular basis:
Rebuilds the index with the new fill (duty) factor to reorganize the data on the data and index pages. This ensures that database pages contain equally distributed data and remaining space, which allows for faster growth in the future. Remove an empty data page from the database page to compress the data file. Update index statistics to ensure that query optimization has table-distributed data that is worth the most up-to-date information. This allows query optimization to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are updated by the SQL Server cycle, this option forces statistics to be updated immediately. Perform internal consistency checks on data and data pages on the database to ensure that system or software problems do not corrupt data backup databases and things logs. Database and date backups can be maintained for a specified period, which allows you to create log transmissions using a backup history of events that you need to restore a database earlier than the last backup. Log transfers allow things to be fed back to another database (the destination) from one database (source). Keeping the destination database synchronized with the original database allows you to have a standby database and also provides a way to remove query processing from the primary database to the read-only destination server
1, Sp_add_maintenance_plan

Add a maintenance plan and return the plan ID

Syntax: Sp_add_maintenance_plan [@plan_name =] ' plan_name ',

@plan_id = ' plan_id ' OUTPUT

Return Value: 0 Success 1 failure

Note: Sp_add_maintenance_plan must be executed on msdb, which creates a new empty maintenance plan. Add one or more databases to perform sp_add_maintenance_plan_db and sp_add_maintenance_plan_job interactions with one or more job jobs.

Only sysadmin fixed server role members can perform Sp_add_maintenance_plan



1, sp_add_maintenance_plan_db

Adding a database to the maintenance plan

Syntax: sp_add_maintenance_plan_db [@plan_id =] ' plan_id ',

[@db_name =] ' database_name '

Return Value: 0 Success 1 failure

Note: sp_add_maintenance_plan_db must be performed on msdb, only members of the sysadmin fixed server role can perform sp_add_maintenance_plan_db



2, Sp_add_maintenance_plan_job

Add a maintenance plan to an existing job

Syntax: sp_add_maintenance_plan_job [@plan_id =] ' plan_id ', [@job_id =] ' job_id '

Return Value: 0 Success 1 failure

Note: Sp_add_maintenance_plan_job must be performed on msdb, only members of the sysadmin fixed server role can perform sp_add_maintenance_plan_db



3, Sp_delete_maintenance_plan

Delete Specified maintenance plan

Syntax: Sp_delete_maintenance_plan [@plan_id =] ' plan_id '

Return Value: 0 Success 1 failure

Note: Sp_delete_maintenance_plan must be performed on msdb, only members of the sysadmin fixed server role can perform sp_add_maintenance_plan_db


4, sp_delete_maintenance_plan_db from the maintenance plan to cancel the specified database
Syntax: sp_delete_maintenance_plan_db [@plan_id =] ' plan_id ',
[@db_name =] ' database_name '

Return Value: 0 Success 1 failure

Note: sp_delete_maintenance_plan_db must be performed on msdb and only the sysadmin fixed server role member can perform sp_delete_maintenance_plan_db. The maintenance plan is also deleted when the last database is deleted from the maintenance plan.
5, Sp_delete_maintenance_plan_job
To cancel (delete) a maintenance plan from a specified job
Syntax: sp_delete_maintenance_plan_job [@plan_id =] ' plan_id ',
[@job_id =] ' job_id '
Return Value: 0 Success 1 failure

Note: Sp_delete_maintenance_plan_job must be performed on msdb and only the sysadmin fixed server role member can perform sp_delete_maintenance_plan_job. The maintenance plan is also deleted when the last database is deleted from the maintenance plan. When all jobs are removed from the maintenance plan the recommended user performs sp_delete_maintenance_plan_db remove the remaining databases from the maintenance plan


6, Sp_help_maintenance_plan
Returns information on the specified maintenance plan and returns all maintenance plan information if no plan is specified

Syntax: Sp_help_maintenance_plan [[@plan_id =] ' plan_id ']

If you specify a schedule, return three sheets: Plan, Database, Job.

Plan

Column Name

Data type

Describe

plan_id

uniqueidentifier

Maintenance Plan ID

Plan_name

sysname

Maintenance plan name.

Date_created

Datetime

Schedule creation date.

Owner

sysname

Plan owner

Max_history_rows

Int

The maximum number of allocated rows that are scheduled to record history in the system table

Remote_history_server

Int

Remote server name to write history report

Max_remote_history_rows

Int

The remote server that writes the history report allocates the maximum number of rows in the system table.

User_defined_1

Int

The default is NULL.

User_defined_2

nvarchar (100)

The default is null.

User_defined_3

Datetime

The default is null.

User_defined_4

uniqueidentifier

The default is null.



Database

Column Name

Describe

database_name

All database names in the maintenance plan database_name is sysname.



Job

Column Name

Describe

Hui

All job IDs in the maintenance plan. job_id is uniqueidentifier.



Returns all schedule information if no plan is specified.

7, Sysdbmaintplan_databases

Each database in a related maintenance plan contains one row, and this table is saved in msdb.

Column Name

Data type

Describe

plan_id

uniqueidentifier

Maintenance Plan ID

database_name

sysname

Database name



8, Sysdbmaintplan_history

Each maintenance plan completes one row at a time, and this table is saved in msdb.

Column Name

Data type

Describe

sequence_id

Int

Maintenance plan execution History sequence number

plan_id

uniqueidentifier

Maintenance Plan ID.

Plan_name

sysname

Maintenance plan name.

database_name

sysname

Related Plan database name

server_name

sysname

System name

Activity

nvarchar (128)

Maintenance plans Activate execution (for example, backup things logs, etc.).

Succeeded

Bit

0 = Success 1 = failure

End_time

Datetime

Time of each action completion

Duration

Int

The length of time the action must be completed

Start_time

Datetime

Action start time

Error_number

Int

Number of failures reported errors

Message

nvarchar (512)

SQLMaint the message that occurred.



9, Sysdbmaintplan_jobs

Each maintenance plan job contains one row. This table is saved in msdb.

Column Name

Data type

Describe

plan_id

uniqueidentifier

Maintenance Plan ID

job_id

uniqueidentifier

Job ID



10, Sysdbmaintplans

Each database maintenance plan contains one row. This table is saved in msdb.

Column Name

Data type

Description

plan_id

uniqueidentifier

Maintenance Plan ID

Plan_name

sysname

Maintenance plan name.

Date_created

Datetime

Schedule creation date.

Owner

sysname

Plan owner

Max_history_rows

Int

The maximum number of allocated rows that are scheduled to record history in the system table

Remote_history_server

sysname

Remote server name to write history report

Max_remote_history_rows

Int

The remote server that writes the history report allocates the maximum number of rows in the system table.

User_defined_1

Int

The default is NULL.

User_defined_2

nvarchar (100)

The default is null.

User_defined_3

Datetime

The default is null.

User_defined_4

uniqueidentifier

The default is null.

Log_shipping

Bit

Log Run Status 0 = expiration 1 = active