Maintenance programs performed automatically:
Reconstructing the index with the new fill (duty cycle) factor to reorganize the data and the data on the index page. This ensures that the database page contains equal distributed data and remaining space, which allows for faster growth in the future.
Show the empty data page compressed data file from the database page.
Update index statistics to ensure that the query optimizes the table distribution data is worth the latest information. This allows query optimization to make a better judgment of the optimal way to access data because it is about more information about data stored in the database. Although index statistics are updated by SQL Server cycle, this option forces the statistics to update now.
Perform data on the database and data pages inside the data page to ensure that the system or software problem does not damage the data.
Back up databases and things logs. Databases and date backups can keep a specified cycle, allowing you to create a history backup history that you need to recover a database than the last backup.
Set log transmission. Log Transfer Allows the Log from a database (source) to another database (purpose). Keep the destination database and the original database to make you have an alternate database and provide a method of removing query processing from the primary database to the read-only destination server.
1, sp_add_maintenance_plan
Add a maintenance plan and return to plan ID
Syntax: sp_add_maintenance_plan [@PLAN_NAME =] 'plan_name',
@PLAN_ID = 'PLAN_ID' OUTPUT
Return value: 0 success 1 failed
Remarks: sp_add_maintenance_plan must be executed on the MSDB, which creates a new air maintenance plan. Increase one or more databases with one or more Job interactively perform sp_add_maintenance_plan_db and sp_add_maintenance_plan_job.
Only SYSADMIN Fixed Server Role Members can perform sp_add_maintenance_plan
1, sp_add_maintenance_plan_db
Add databases to the maintenance plan
Syntax: sp_add_maintenance_plan_db [@PLAN_ID =] 'Plan_ID',
[@db_name =] 'Database_name'
Return value: 0 success 1 failed
Remarks: sp_add_maintenance_plan_db must be executed on the MSDB, only SP_ADD_MAINTENANCE_PLAN_DB is executed on the MSDB
2, sp_add_maintenance_plan_job
Add the 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 failed
Note: sp_add_maintenance_plan_job must be executed on the MSDB, only SYSADMIN fixed server role members can perform sp_add_maintenance_plan_db
3, sp_delete_maintenance_plan
Delete the designated maintenance plan
Syntax: sp_delete_maintenance_plan [@PLAN_ID =] 'Plan_ID'
Return value: 0 success 1 failed
Remarks: sp_delete_maintenance_plan must be executed on the MSDB, only SYSADMIN fixed server role members can perform sp_add_maintenance_plan_db
4, sp_delete_maintenance_plan_db
Cancel the specified database syntax from the maintenance plan: sp_delete_maintenance_plan_db [@PLAN_ID =] 'PLAN_ID', [@db_name =] 'Database_name'
Return value: 0 success 1 failed
Note: sp_delete_maintenance_plan_db must be executed on the MSDB, 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
Cancel (delete) maintenance plan from specified JOB
Syntax: sp_delete_maintenance_plan_job [@PLAN_ID =] 'Plan_ID', [@JOB_ID =] 'JOB_ID'
Return value: 0 success 1 failed
Note: sp_delete_maintenance_plan_job must be executed on the MSDB, 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 remove the recommended user from the maintenance plan, the SP_DELETE_MAINTENANCE_PLAN_DB removes the remaining database from the maintenance plan.
6, sp_help_maintenance_plan
Returns information about the specified maintenance plan, if you do not specify a plan, return all maintenance plan information
Syntax: sp_help_maintenance_plan [[@PLAN_ID =] 'PLAN_ID']
Returns three tables: Plan, Database, Job.
Plan
Column name
type of data
description
PLAN_ID
UniqueIdentifier
Maintenance plan ID
Plan_name
Sysname
Maintenance plan name.
Date_created
Datetime
Plan to create a date.
Owner
Sysname
Plan owner
MAX_HISTORY_ROWS
int
Planning the maximum distribution line of history in the system table
REMOTE_HISTORY_SERVER
int
Remote server name of history report
MAX_REMOTE_HISTORY_ROWS
int
Write a history report to the maximum allocation line of 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
description
Database_name
All database name Database_name is sysname in the maintenance plan.
Job
Column name
description
HUI
Maintain all Job IDs in the program. Job_ID is UniqueIdentifier.
Returns all scheduled information if you do not specify a plan.
7, sysdbmaintplan_databases
Each of the related maintenance plans contains a row, and this table is saved in the MSDB.
Column name
type of data
description
PLAN_ID
UniqueIdentifier
Maintenance plan ID
Database_name
Sysname
data storage name
8, SYSDBMAINTPLAN_HISTORY
Each maintenance schedule completes a row at a time, and this table is saved in the MSDB.
Column name
type of data
description
SEQUENCE_ID
int
Maintenance plan Execute history sequence 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 schedule activates execution (such as backup event logs, etc.).
succeeded
Bit
0 = Success 1 = failed
END_TIME
Datetime
Each action completion time
Duration
int
The length of time must be completed
START_TIME
Datetime
Action start time
Error_Number
int
Failure report number
Message
NVARCHAR (512)
Sqlmaint message occurred.
9, SYSDBMAINTPLAN_JOBS
Each maintenance plan JOB contains a line. This table is saved in the MSDB.
Column name
type of data
description
PLAN_ID
UniqueIdentifier
Maintenance plan ID
Job_ID
UniqueIdentifier
Job ID
10, sysdbmaintplans
Each database maintenance plan contains a line. This table is saved in the MSDB.
Column name
Data Type
Description
PLAN_ID
UniqueIdentifier
Maintenance plan ID
Plan_name
Sysname
Maintenance plan name.
Date_created
Datetime
Plan to create a date.
Owner
Sysname
Plan owner
MAX_HISTORY_ROWS
int
Planning the maximum distribution line of history in the system table
REMOTE_HISTORY_SERVER
Sysname
Remote server name of history report
MAX_REMOTE_HISTORY_ROWS
int
Write a history report to the maximum allocation line of 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 = Failure 1 = Activation
