SQL Server maintenance plan

Scheduled maintenance plans:

Use the New fill (empty) factor to rebuild the index to restructure the data and the data on the index page. This ensures that the database page contains equally distributed data and available 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 the query is optimized and the table distribution data is worth the latest information. This allows query optimization to make a better judgment on the optimal way to access data, because it has more information about the data stored in the database. Although index statistics are updated by SQL Server periodically, this option forces statistics to be updated immediately. Perform internal consistency check on data and data pages on the database to ensure that system or software problems do not damage the data backup database and transaction logs. Database and date backup can maintain a specified cycle, which allows you to create and use the event backup history setting log transmission before you need to recover the database. Log transmission allows transaction logs to be repeatedly fed back to another database from one database (source ). Keep the synchronization between the target database and the original database so that you have a backup database and provide a way to unload the query from the master database to the read-only target server.

1. sp_add_maintenance_plan

Add a maintenance plan and return the plan ID

Syntax:

Double-click all code

12	sp_add_maintenance_plan [ @plan_name = ] 'plan_name' , @plan_id = 'plan_id' OUTPUT

Returned 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 interact with one or more jobs and execute sp_add_maintenance_plan_db and sp_add_maintenance_plan_job.

Only SysAdmin fixed server role members can execute sp_add_maintenance_plan

2. sp_add_maintenance_plan_db

Add the database to the maintenance plan

Syntax:

Double-click all code

12	sp_add_maintenance_plan_db [ @plan_id = ] 'plan_id' , [ @db_name = ] 'database_name'

Returned value: 0 Success 1 Failure

Note: sp_add_maintenance_plan_db must be executed on MSDB. Only members of the SysAdmin fixed server role can execute sp_add_maintenance_plan_db.

3. sp_add_maintenance_plan_job

Add the maintenance plan to an existing job.

Syntax:

Double-click all code

1	sp_add_maintenance_plan_job [ @plan_id = ] 'plan_id', [ @job_id = ] 'job_id'

Returned value: 0 Success 1 Failure

Note: sp_add_maintenance_plan_job must be executed on MSDB. Only members of the SysAdmin fixed server role can execute sp_add_maintenance_plan_db.

4. sp_delete_maintenance_plan

Delete a specified maintenance plan

Syntax:

Double-click all code

1	sp_delete_maintenance_plan [ @plan_id = ] 'plan_id'

Returned value: 0 Success 1 Failure

Note: sp_delete_maintenance_plan must be executed on MSDB. Only members of the SysAdmin fixed server role can execute sp_add_maintenance_plan_db.

5. sp_delete_maintenance_plan_db cancels the specified database from the maintenance plan

Syntax:

Double-click all code

12	sp_delete_maintenance_plan_db [ @plan_id = ] 'plan_id' , [ @db_name = ] 'database_name'

Returned value: 0 Success 1 Failure

Note: sp_delete_maintenance_plan_db must be executed on MSDB. Only members of the SysAdmin fixed server role can execute sp_delete_maintenance_plan_db. When the last database is deleted from the maintenance plan, the maintenance plan is also deleted.

6. sp_delete_maintenance_plan_job

Cancels (deletes) the maintenance plan from the specified job.

Syntax:

Double-click all code

12	sp_delete_maintenance_plan_job [ @plan_id = ] 'plan_id' , [ @job_id = ] 'job_id'

Returned value: 0 Success 1 Failure

Note: sp_delete_maintenance_plan_job must be executed on MSDB. Only members of the SysAdmin fixed server role can execute sp_delete_maintenance_plan_job. When the last database is deleted from the maintenance plan, the maintenance plan is also deleted. When all jobs are deleted from the maintenance plan, it is recommended that the user execute sp_delete_maintenance_plan_db to remove the remaining database from the maintenance plan.

7. sp_help_maintenance_plan

Returns the information of the specified maintenance plan. If no plan is specified, all maintenance plan information is returned.

Syntax:

Double-click all code

1	sp_help_maintenance_plan [ [ @plan_id = ] 'plan_id' ]

If a plan is specified, three tables: Plan, database, and job are returned.

Plan

Column name

Data Type

Description

Plan_id

Uniqueidentifier

Maintenance Plan ID

Plan_name

Sysname

Maintenance Plan Name.

Date_created

Datetime

Planned creation date.

Owner

Sysname

Plan owner

Max_history_rows

Int

Maximum number of allocated rows planned to be recorded in the system table

Remote_history_server

Int

Name of the remote server that writes the history report

Max_remote_history_rows

Int

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

User_defined_1

Int

The default value is null.

User_defined_2

Nvarchar (100)

The default value is null.

User_defined_3

Datetime

The default value is null.

User_defined_4

Uniqueidentifier

The default value is null.

Database

Column name

Description

Database_name

All Database names in the maintenance plan database_name is sysname.

Job

Column name

Description

Hui

Maintain all job IDs in the scheduler. job_id is uniqueidentifier.

If no plan is specified, all plan information is returned.

8. sysdbmaintplan_databases

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

Column name

Data Type

Description

Plan_id

Uniqueidentifier

Maintenance Plan ID

Database_name

Sysname

Database Name

9. sysdbmaintplan_history

Each maintenance plan contains one row at a time, and the table is saved in MSDB.

Column name

Data Type

Description

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)

The maintenance plan is activated for execution (such as backing up transaction logs ).

Succeeded

Bit

0 = Success 1 = failure

End_time

Datetime

Completion Time of each action

Duration

Int

Duration of the action that must be completed

Start_time

Datetime

Action Start Time

Error_number

Int

Number of error reports

Message

Nvarchar (512)

Message generated by sqlmaint.

10. sysdbmaintplan_jobs

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

Column name

Data Type

Description

Plan_id

Uniqueidentifier

Maintenance Plan ID

Job_id

Uniqueidentifier

Job ID

11. 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

Planned creation date.

Owner

Sysname

Plan owner

Max_history_rows

Int

Maximum number of allocated rows planned to be recorded in the system table

Remote_history_server

Sysname

Name of the remote server that writes the history report

Max_remote_history_rows

Int

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

User_defined_1

Int

The default value is null.

User_defined_2

Nvarchar (100)

The default value is null.

User_defined_3

Datetime

The default value is null.

User_defined_4

Uniqueidentifier

The default value is null.

Log_shipping

Bit

Log running status 0 = Invalid 1 = activated