SQL Server Maintenance Plan maintenance plans

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

Grammar:

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

2, sp_add_maintenance_plan_db

Adding a database to the maintenance plan

Grammar:

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

3, Sp_add_maintenance_plan_job

Add a maintenance plan to an existing job

Grammar:

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

4, Sp_delete_maintenance_plan

Delete Specified maintenance plan

Grammar:

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

5, sp_delete_maintenance_plan_db from the maintenance plan to cancel the specified database

Grammar:

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