SQL Server 2005 Database Maintenance plan

Starting this week to ease work pressure. Start using Database maintenance plans (SQL Server Maintenance plan Wizard) to maintain the database, since it has not previously been used, in the personal use of the free version (Express) does not have this function, so now learn a good , here's a summary.

The Maintenance Plan Wizard can be used to help you set up core maintenance tasks to ensure that the database is performing well, to regularly back up the database in case of system failure, and to perform an inconsistency check on the database. The Maintenance Plan Wizard creates one or more SQL Server Agent jobs that are automatically performed by the agent job at scheduled intervals. It enables you to perform various database management tasks, including backups, running database integrity checks, or updating database statistics at specified intervals. Creating a Database maintenance plan allows SQL Server to effectively automate the maintenance of databases, keep the database running at optimal state, and save administrators valuable time.

Here are some maintenance tasks that you can schedule to run automatically:

Regenerate the index with the new fill factor to rearrange the data on the data and index pages. This ensures that the amount of data that is contained in the database pages and the average distribution of available space are also allowed to grow faster in the future.

Compresses a data file by deleting an empty database page.

Update the index statistics to ensure that the query optimizer contains the most up-to-date information about the distribution of data values in the table. This allows the query optimizer to better determine the best way to access the data, because you can get detailed information about the data stored in the database. Although SQL Server automatically updates index statistics on a regular basis, this option can immediately force updates on statistics.

Perform internal consistency checks on data and data pages in the database to ensure that there are no corrupted data for system or software failures.

Back up the database and transaction log files. Database and log backups can be retained for a specified period of time. This allows you to create a history of the backup to use when you need to restore the database to a time earlier than the last database backup. You can also perform differential backups.

Run the SQL Server agent job. This can be used to create jobs that perform various operations and maintenance plans that run these jobs.

The results generated by the maintenance task can be written to a text file as a report, or to the Sysmaintplan_log and sysmaintplan_log_detail maintenance schedules in msdb. To view the results in the Log File Viewer, right-click Maintenance Plan, and then click View History.

Here is a detailed description:

Check database Integrity (checking DB integrity)

Task checks the allocation and structural integrity of all objects in the specified database. This task can examine a single database or multiple databases, and you can choose whether to also check the database index, check the integrity of all index pages, and table data pages.

This task encapsulates the DBCC CHECKDB statement.

Generated code:

--Check the current database, cancel informational messages

DBCC CHECKDB with No_infomsgs

Shrink db (Shrinking database Task)

Shrink database ' Tasks ' dialog box you can create a task to try to reduce the size of the selected database.

This task encapsulates the DBCC shrinkdatabase command.

Options:

Shrink database when it grows beyond

Shrinks the database when the database size exceeds the specified value, specifying the database size in megabytes (MB) that this task is raised for.

Amount of free spaces to remain after shrink

The free space that is retained after shrinkage to stop shrinking when the free space in the database file reaches this value.

Retain freed spaces in database files

Choose to keep the freed file space in the database file. If you specify the Notruncate option, the data file does not appear to shrink.

Return freed spaces to operating system

Choose to release any unused space in the data file to the operating system. Reduce file size without moving any data.

Generated code:

--Select retain freed spaces in database files

DBCC shrinkdatabase (N ' AdventureWorks ', ten, Notruncate)

--Select return freed spaces to operating system

DBCC shrinkdatabase (N ' AdventureWorks ', ten, truncateonly)

Reorganize Index (re-organizing indexes)

Re-organize indexes in SQL Server database tables and views. By using the Re-organize index task, a package can rearrange indexes in a single database or in multiple databases. If this task only organizes indexes in a single database, you can select the view or table whose indexes you want the task to rearrange. The Re-organize index task also contains options for compressing large object data. Large object data is data that has image, text, ntext, varchar (max), nvarchar (max), varbinary (max), or XML data type.

This task encapsulates the Transact-SQL ALTER INDEX statement.

If you choose to compress large object data, the statement uses the REORGANIZE with (lob_compaction = ON) clause, or the lob_compaction is set to OFF.

Generate code: (Only the Employee table is selected)

--Select compact Large objects

ALTER INDEX [Pk_employee_employeeid] on [HumanResources]. [Employee] REORGANIZE with (lob_compaction = ON)

--No choice

ALTER INDEX [Pk_employee_employeeid] on [HumanResources]. [Employee] REORGANIZE with (lob_compaction = off)

Rebuild Index (Rebuild indexes)

Rebuilds the indexes in SQL Server database tables and views. Packages can regenerate indexes in a single database or in multiple databases. If the task rebuilds only the indexes in a single database, you can select the view and table whose indexes you want the task to regenerate. Rearrange the pages with the default free space to delete the indexes on the tables in the database and re-create the indexes using the fill factor specified when the index was created.

This task encapsulates the ALTER index REBUILD statement and provides the following index regeneration options:

Reorganize pages with the default amount

Specify a FILLFACTOR percentage or use the original FILLFACTOR amount.

Change free spaces per page percentage to:

Populating indexes use the PAD_INDEX option to set the percentage of free space in an intermediate page during the index creation process. Change the percentage of free space per page, delete the indexes on the tables in the database, and re-create the index with the new, automatically calculated fill factor, thereby reserving the specified free space on the index page.

Sort results in tempdb

Using the SORT_IN_TEMPDB option, this option determines the temporary storage location of intermediate sort results generated during index creation. Use the IGNORE_DUP_KEY option of the index to specify an error response to duplicate key values in a multiline INSERT transaction on a unique clustered or nonclustered index.

Keep Index Online while reindexing

Using the ONLINE option, users can access the underlying table or clustered index data and any associated nonclustered indexes during the index operation.

Generate code: (Only the Employee table is selected)

ALTER INDEX [Pk_employee_employeeid] on [HumanResources]. [Employee]  REBUILD with (FILLFACTOR = the Pad_index = off, Statistics_norecompute = off, Allow_row_locks = ON, allow_page_locks = ON, sort_in_tempdb = off, ONLINE = off)

Updata Statics (UPDATE STATISTICS)

Updates the key value distribution information for one or more statistics groups (collections) in the specified table or indexed view.

This task encapsulates the UPDATE STATISTICS statement.

All existing statistics

If the update applies to all statistics, the use of the WITH-all clause is implied.

Column Statistics only

If the update applies only to columns, the WITH COLUMN clause is included.

Index Statistics only

If the update applies only to the index, the WITH index clause is included.

Full scan

All statistics

Sample by

The size of the data sampled from the table corresponding to each index depends on the number of rows in the table and how often the data is modified.

Generate code: (Only the Employee table is selected)
UPDATE STATISTICS [HumanResources]. [Employee]

With Fullscan

Clean up History (purge history)

Use the Purge History dialog box to discard old historical information from the msdb database table. This task supports the deletion of backup and restore history, Microsoft SQL Server Agent job history, and maintenance plan history.

This task encapsulates the sp_delete_backuphistory system stored procedure and passes the specified date as a parameter to the procedure.

Options:

Backup and restore history

SQL Server Agent Job history

Maintenance Plan History

Generate Code:

The following are the referenced contents: DECLARE @dt datetime Select @dt = Cast (N ' 2007-05-31t08:00:00 ' as datetime) EXEC msdb.dbo.sp_delete_backuphistory @dt Go EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @dt Go EXECUTE msdb.. Sp_maintplan_delete_log Null,null, @dt Go

Execute SQL Server Agent job (Execute SQL Server Proxy job)

The task runs the SQL Server agent job. SQL Server Agent jobs can automate tasks that you need to perform repeatedly.

This task encapsulates the Sp_start_job system procedure and passes the name of the SQL Server agent job as a parameter to the procedure.

Back Up Database Task

Backup used, too familiar, not introduced.

Maintenance Cleanup Task

This task encapsulates the Master.dbo.xp_delete_file system procedure used to delete a backup file.

Execute T-SQL Statement Task

Execute a T-SQL task to run a Transact-SQL statement. This task is not available when using a wizard and is to be dragged out of the design view.

Notify Operator Task

Notifies the operator that the task sends a notification message to the SQL Server agent operator. This task is the only database maintenance task that does not encapsulate Transact-SQL statements or DBCC commands.

It is best to perform maintenance plans in a certain order, first by checking database integrity, then by shrinking the database, by rebuilding the index, or by organizing the indexing task, and finally by updating the statistics.

Rebuilding an index or organizing an index to choose a different action depending on the situation, the two choices have little meaning. The first step in deciding which defragmentation method to use is to analyze the index to determine the extent of fragmentation. You can use System functions sys.dm_db_index_physical_stats to detect all the indexes of a particular index, table, or indexed view, all indexes in a database, or fragments in all indexes in all databases. Once you know the extent of fragmentation, you can determine the best way to repair fragmentation. Index fragmentation is not too much, you can organize the index again. However, if the index fragment is very much, rebuilding the index will yield better results.

Our company these tasks are one weeks run once, several databases add up to have 200G, the database is not very big, each run to two hours or more, so are in the early morning. If you are rebuilding the index then the table is inaccessible at execution time and there is no better solution now. This question still continues to study!