Shulou(Shulou.com)06/01 Report--

This article mainly introduces the advantages of using maintenance plan in sql server2005. It is very detailed and has certain reference value. Friends who are interested must finish it.

Database maintenance Plan metadata Store in SQL Server 2000: msdb.dbo.sysdbmaintplan_databases msdb.dbo.sysdbmaintplan_history msdb.dbo.sysdbmaintplan_jobs msdb.dbo.sysdbmaintplans

Execution of database maintenance plan in SQL Server 2000: jobs managed by SQL Agent call xp_sqlmaint external tool sqlmaint.exe

Maintenance plan under SQL Server 2005: design through SQL Server Integration Service-- > Design in Management Studio Design in Development Studio call SSIS package through jobs A maintenance plan generates only one job

How to use Management Studio to create maintenance plan and schedule and execute maintenance plan?

Expand SQL2005 (Local computer name)-- manage-- right click on the maintenance plan-- Select the maintenance plan wizard and then next note: configuring the SQL Server maintenance plan is a prerequisite to make sure that the SQL Server agent service is enabled.

Let's call it MP Demo Wizard. Go on to the next step.

In the selected maintenance task, check the database integrity, rebuild the index, update statistics, clear the history, and back up the database (full) on the next step

If you want to adjust the order of these tasks, you can move up or down to move on to the next step.

Press OK on the AdventureWorks ditch in the following database of the database and proceed to the next step.

In the following database of the database, press OK to select table in object and select all to change the percentage of free space per page to 50% in the next step.

In the following database of the database, press OK on the AdventureWorks ditch to select the table in the object, select all in the selection, select all existing statistics in the update, and then proceed to the next step.

Move on to the next step.

In the following database, click OK to enter a path in the backup database across one or more files-- > C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Backup\ bak to continue to the next step.

Write the report to the text file ditch and move on to the next step

Press finish

You can see that the status is all successful. press close to expand management-- maintenance plan-- you can see how a maintenance task called MP Demo Wizard is performed. Expand the SQL Server agent-- jobs-- you can see a job called MP Demo Wizard Subplan_1 that invokes the maintenance plan's

Where is the maintenance plan kept? Click connection in object Explorer-- Select Integration Services in the server type, expand SQL2005 (Integration Services...) by connection to the server-- stored packages-- MSDB--Maintenance Plans-- can see the maintenance task of MP Demo Wizard that I just designed

Expand the SQL Server Agent-- Job-- right-click the MP Demo Wizard Subplan_1-- Select Properties-- follow the steps-- you can see a job step called Subplan_1. Double-click the job step to open it.

You can see that the type of job task is the SQL Server Integration Services package. In fact, it invokes a SSIS package like MP Demo Wizard just generated through the SQL proxy service account.

In addition to implementing the maintenance plan through the wizard, we can also directly create a new maintenance plan for management-- right-click the maintenance plan-- select a new maintenance plan-- the name is MP Demo SSMS Bar. Press OK.

Here we can also design a complete job task to drag "check database integrity" to it-- double-click the task-- and press OK on the AdventureWorks ditch in the following database.

What if I want to go to the "rebuild index" task after checking the integrity of the database? You can drag the "rebuild Index" task to check the integrity of the database and double-click the task to open it-- select AdventureWorks in the database-- select the table in the object-- select all in the selection-- change the percentage of free space per page to 50% by OK.

Drag the "update statistics" task to the "rebuild index" task, select AdventureWorks in the database, select the database in the object, select the table in the selection, select all in the update, select all the existing statistics, click OK.

Drag the backup Database task to the bottom of the Update Statistics task and double-click the task to open it-- Select complete in the backup type-- select AdventureWorks as the database in the database-- enter bak-- in the backup file extension to verify the backup integrity ditch-- press OK.

Such an assignment is finished. I set it to be executed at 0:00:00 every Sunday. Press the save icon on it and ok it.

Press F5 to refresh and you can see that there is an extra maintenance task called MP Demo SSMS in the maintenance plan. Expand the SQL Server agent-- job-- you can see an extra job called MP Demo SSMS Subplan_1, expand SQL2005 (Integration Services...)-- the stored package-- MSDB--Maintenance Plans can see that there is an extra package called MP Demo SSMS.

Operations supported by the SQL Server 2005 maintenance plan: database backup data integrity check execution SQL Agent job execution T-SQL script historical data cleanup maintenance plan data clean up reindexing reindexing shrinking database update statistics

The T-SQL statements related to the maintenance plan (1)-- > see the following list:

Maintenance plan T-SQL statement

Database backup BACKUP DATABASE BACKUP LOG

Data integrity check DBCC CHECKDB

Execute SQL Agent job sp_start_job

Execute the T-SQL script sp_executesql

Execute

Historical data cleaning sp_purgejob_history

Sp_delete_backuphistory

Sp_maintplan_delete_log

T-SQL statements related to the maintenance plan (2)-- > see the following list:

Maintenance plan T-SQL statement

Maintenance plan data cleaning xp_delete_file

Re-index ALTER INDEX index_name

REBUILD WITH params

DBCC DBREINDEX*

Reorganize the index ALTER INDEX index_name

REORGANIZE WITH params

DBCC INDEXDREFREG*

Shrink database DBCC SHRINKDATABASE

Update Statistics UPDATE STATISTICS

Metadata storage and retrieval of maintenance plans: reserved for compatibility-- > msdb.dbo.sysdbmaintplans msdb.dbo.sysdbmaintplan_jobs msdb.dbo.sysdbmaintplan_databases msdb.dbo.sysdbmaintplan_history msdb.dbo.sp_help_maintenance_plan

System maintenance plan-- > msdb.dbo.sysdtspackages90 msdb.dbo.sysmaintplan_subplans msdb.dbo.sysmaintplan_log msdb.dbo.sysmaintplan_logdetail

Use Development Studio to design maintenance plan: maintenance plan is based entirely on Integration Service maintenance plan to generate SSIS Package SQL Agent job call SSIS Package to use Development Studio to add other complex tasks to the maintenance plan

Special note: do not delete the maintenance plan by deleting the SSIS Package, otherwise the metadata will remain in the msdb.dbo.sysmaintplan_subplans system table.

Advantages of maintenance plans in SQL Server 2005: the process of customizable maintenance jobs can add complex logic to maintenance jobs to make it easier to export and replicate across servers to link up with data maintenance and business maintenance jobs

These are all the contents of the article "what are the advantages of using maintenance plans in sql server2005". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.