A brief analysis of SSIS Maintenance Plan in SQL Server 2005

Each version of SQL Server has gradually become more and more self aligning and maintainable. However, requirements for basic maintenance, such as deleting index fragments, updating statistics, checking database consistency, and making backups are still not disappearing. As you can imagine, the maintenance of an index is like changing oil to your car, and backing up is like buying insurance. You can have no insurance, but once you need it, it's on hand. In the same way, your car can run thousands of kilometers without oil change, but regular maintenance will produce better performance and allow you to extend your car's life for several years.

SQL Server 2005 implements the maintenance plan as an SSIS package, which provides greater flexibility for the customization of the task. Now start planning the Transact-SQL commands you want to execute to view them, rather than using stored procedures in SQL Server 2000 that are not documented. Another area you can examine is execution history, which can make the maintenance plan for problem solving a lot simpler.

Maintenance plan in SQL Server 2000

Let's briefly review what the database maintenance plan is like in SQL Server 2000. You create a maintenance plan by clicking on the wizard's interface, which allows you to choose a full backup, transaction date to backup, index maintenance, and check database consistency options. That sounds simple, doesn't it? Yes, unless the maintenance plan is a "black box" wizard and does not show you the Transact-SQL statement that the task you just created will execute.

Further, if you've ever checked the properties of a task created by the Maintenance Plan Wizard, you'll know that the step of the task looks like this:

EXECUTE master.dbo.xp_sqlmaint N '-planid 7bccf8e2-b8f4-4b92-bc3b-

F2fa1bae25c0-writehistory-vrfybackup-bkupmedia Disk-bkupdb-usedefdir-bkext "BAK"

To understand what's behind this maintenance plan screen, you have to familiarize yourself with the Xp_sqlmaint extended storage program. This program is also called the sqlmaint tool, there is probably a dozen switches. In addition, you can also invoke sqlmaint from the command line. If the maintenance Plan task has failed in the history of the task, it may be more important to display the following fragments of information that are completely meaningless in the task history: