Use the Maintenance Plan Wizard - OSAS
[Pages:13]SQL Maintenance Plan Wizard
For the most up-to-date information or for a previous version of the SQL Server Maintenance Plan Wizard, visit: The following is taken directly from Microsoft's website, as it appeared on 8.10.12:
Use the Maintenance Plan Wizard
This topic describes how to create a single server or multiserver maintenance plan using the Maintenance Plan Wizard in SQL Server 2012. The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.
In This Topic
Before you begin:
Limitations and Restrictions
Security
Creating a maintenance plan using the Maintenance Plan Wizard in SQL Server Management Studio
Before You Begin
Limitations and Restrictions
To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured. Multiserver maintenance plans must be created and maintained on the master server. These plans can be viewed, but not maintained, on target servers.
Members of the db_ssisadmin and dc_admin roles may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages; these packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.
Security
Permissions
To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
Using Maintenance Plan Wizard
SQL Maintenance Plan Wizard
To start the Maintenance Plan Wizard
1. Expand the server where you want to create your management plan. 2. Expand the Management folder. 3. Right-click the Maintenance Plans folder and select Maintenance Plan Wizard. 4. On the SQL Server Maintenance Plan Wizard page, click Next. 5. On the Select Plan Properties page:
a. In the Name box, enter the name of the maintenance plan you are creating. b. In the Description box, briefly describe your maintenance plan. c. In the Run as list, specify the credential that Microsoft SQL Server Agent uses when executing the maintenance
plan. d. Select either Separate schedules for each task or Single schedule for the entire plan or no schedule to
specify the recurring schedule of the maintenance plan.
Note
If you select Separate schedules for each task, you will need to follow the steps in e. below for each task in your mainte
plan.
e. If you selected Single schedule for the entire plan or no schedule, under Schedule, click Change. a. In the New Job Schedule dialog box, in the Name box, enter the job schedule's name. b. On the Schedule type list, select the type of schedule: Start automatically when SQL Server Agent starts Start whenever the CPUs become idle Recurring. This is the default selection. One time c. Select or clear the Enabled check box to enable or disable the schedule. d. If you select Recurring: a. Under Frequency, on the Occurs list, specify the frequency of occurrence: If you select Daily, in the Recurs every box, enter how often the job schedule repeats in days. If you select Weekly, in the Recurs every box, enter how often the job schedule repeats in weeks. Select the day or days of the week on which the job schedule is run. If you select Monthly, select either Day or The. If you select Day, enter both the date of the month you want the job schedule to run and how often the job schedule repeats in months. For example, if you want the job schedule to run on the 15th day of the month every other month, select Day and enter "15" in the first box and "2" in the second box. Please note that the largest number allowed in the second box is "99". If you select The, select the specific day of the week within the month that you want the job schedule to run and how often the job schedule repeats in months. For example, if you want the job schedule to run on the last weekday of the month every other month, select Day, select last from the first list and weekday from the second list, and then enter "2" in the last box. You can also select first, second, third, or fourth, as well as specific weekdays (for example: Sunday or
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
Wednesday) from the first two lists. Please note that the largest number allowed in the last box is "99". b. Under Daily frequency, specify how often the job schedule repeats on the day the job schedule runs: If you select Occurs once at, enter the specific time of day when the job schedule should run in the Occurs once at box. Enter the hour, minute, and second of the day, as well as AM or PM. If you select Occurs every, specify how often the job schedule runs during the day chosen under Frequency. For example, if you want the job schedule to repeat every 2 hours during the day that the job schedule is run, select Occurs every, enter "2" in the first box, and then select hour(s) from the list. From this list you can also select minute(s) and second(s). Please note that the largest number allowed in the first box is "100".
In the Starting at box, enter the time that the job schedule should start running. In the Ending at box, enter the time that the job schedule should stop repeating. Enter the hour, minute, and second of the day, as well as AM or PM.
c. Under Duration, in Start date, enter the date that you want the job schedule to start running. Select End date or No end date to indicate when the job schedule should stop running. If you select End date, enter the date that you want to job schedule to stop running.
e. If you select One Time, under One-time occurrence, in the Date box, enter the date that the job schedule will be run. In the Time box, enter the time that the job schedule will be run. Enter the hour, minute, and second of the day, as well as AM or PM.
f. Under Summary, in Description, verify that all job schedule settings are correct. g. Click OK. f. Click Next. 6. On the Select Target Servers page, select the servers where you want to run the maintenance plan. This page is only visible on SQL Server instances that are configured as master servers.
Note
To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured, and the local server should be configured as a master server. In multiserver environments, this page displays the (local) master server and all corresponding target servers.
7. On the Select Maintenance Tasks page, select one or more maintenance tasks to add to the plan. When you have selected all of the necessary tasks, click Next.
Note
The tasks you select here will determine which pages you will need to complete after the Select Maintenance Task Order page below.
8. On the Select Maintenance Task Order page, select a task and click either Move Up... or Move Down... to change its order of execution. When finished, or if you are satisfied with the current order of tasks, click Next.
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
Note
If you selected Separate schedules for each task on the Select Plan Properties page above, you will not be able to change the order of the maintenance tasks on this page.
Define Database Check Integrity (CHECKDB) Tasks
On the Define Database Check Integrity Task page, choose the database or databases where the allocation and structural integrity of user and system tables and indexes will be checked. By running the DBCC CHECKDB Transact-SQL statement, this task ensures that any integrity problems with the database are reported, thereby allowing them to be addressed later by a system administrator or database owner. For more information, see DBCC CHECKDB (TransactSQL)When complete, click Next.
The following options are available on this page.
Databases list
Specify the databases affected by this task.
All databases
Generate a maintenance plan that runs this task against all Microsoft SQL Server databases except tempdb.
System databases
Generate a maintenance plan that runs this task against SQL Server system databases except tempdb and usercreated databases.
All user databases (excluding master, model, msdb, tempdb)
Generate a maintenance plan that runs this task against all user-created databases. No maintenance tasks are run against the SQL Server system databases.
These databases
Generate a maintenance plan that runs this task against only those databases that are selected. At least one database in the list must be selected if this option is chosen.
Include indexes check box
Check the integrity of all the index pages as well as the table data pages.
Define Database Shrink Tasks
On the Define Shrink Database Task page, create a task that attempts to reduce the size of the selected databases by using the DBCC SHRINKDATABASE statement, with either the NOTRUNCATE or TRUNCATEONLY option. For more information, see DBCC SHRINKDATABASE (Transact-SQL). When complete, click Next.
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
Caution
Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.
The following options are available on this page. Databases list
Specify the databases affected by this task. See step 9, above, for more information on the available options on this list. Shrink database when it grows beyond box
Specify the size in megabytes that causes the task to execute. Amount of free space to remain after shrink box
Stop shrinking when free space in database files reaches this size (as a percentage). Retain freed space in database files
The database is condensed to contiguous pages but the pages are not deallocated, and the database files do not shrink. Use this option if you expect the database to expand again, and you do not want to reallocate space. With this option, the database files do not shrink as much as possible. This uses the NOTRUNCATE option.
Return freed space to operating system The database is condensed to contiguous pages and the pages are released back to the operating system for use by other programs. This database files shrink as much as possible. This uses the TRUNCATEONLY option. This is the default option.
Define the Index Tasks
1. On the Define Reorganize Index Task page, select the server or servers where you'll be moving index pages into a more efficient search order. This task uses the ALTER INDEX ... REORGANIZE statement. For more information, see ALTER INDEX (Transact-SQL). When complete, click Next.
The following options are available on this page.
Databases list
Specify the databases affected by this task. See step 9, above, for more information on the available options on this list.
Object list
Limit the Selection list to display tables, views, or both. This list is only available if a single database is chosen from the Databases list above.
Selection list
Specify the tables or indexes affected by this task. Not available when Tables and Views is selected in the Object box.
Compact large objects check box
Deallocate space for tables and views when possible. This option uses ALTER INDEX ... LOB_COMPACTION = ON.
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
2. On the Define Rebuild Index Task page, select the database or databases where you'll be re-creating multiple indexes. This task uses the ALTER INDEX ... REBUILD PARTITION statement. For more information, see ALTER INDEX (Transact-SQL).) When complete, click Next.
The following options are available on this page.
Databases list
Specify the databases affected by this task. See step 9, above, for more information on the available options on this list.
Object list
Limit the Selection list to display tables, views, or both. This list is only available if a single database is chosen from the Databases list above.
Selection list
Specify the tables or indexes affected by this task. Not available when Tables and Views is selected in the Object box.
Free space options area
Presents options for applying fill factor to indexes and tables.
Default free space per page
Reorganizes the pages with the default amount of free space. This will drop the indexes on the tables in the database and re-create them with the fill factor that was specified when the indexes were created. This is the default option.
Change free space per page to box
Drop the indexes on the tables in the database and re-create them with a new, automatically calculated fill factor, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages, and the larger the index grows. Valid values are from 0 through 100. Uses the FILLFACTOR option.
Advanced options area
Presents additional options for sorting indexes and reindexing.
Sort results in tempdb check box
Uses the SORT_IN_TEMPDB option which determines where the intermediate sort results, generated during index creation, are temporarily stored. If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.
Keep index online while reindexing check box
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
Uses the ONLINE option which allows users to access the underlying table or clustered index data and any associated nonclustered indexes during index operations. Selecting this option activates additional options for rebuilding indexes that do not allow for online rebuilds: Do not rebuild indexes and Rebuild indexes offline.
Note
Online index operations are not available in every edition of SQL Server 2012. For more information, see Features Supported by the Editions of SQL Server 2012.
Define the Update Statistics Task
On the Define Update Statistics Task page, define the database or databases on which table and index statistics will be updated. This task uses the UPDATE STATISTICS statement. For more information, see UPDATE STATISTICS (Transact-SQL) When finished, click Next The following options are available on this page.
Databases list Specify the databases affected by this task. See step 9, above, for more information on the available options on this list.
Object list Limit the Selection list to display tables, views, or both. This list is only available if a single database is chosen from the Databases list above.
Selection list Specify the tables or indexes affected by this task. Not available when Tables and Views is selected in the Object box.
All existing statistics Update statistics for both columns and indexes.
Column statistics only Only update column statistics. Uses the WITH COLUMNS option.
Index statistics only Only update index statistics. Uses the WITH INDEX option.
Scan type Type of scan used to gather updated statistics.
Full scan
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
SQL Maintenance Plan Wizard
Read all rows in the table or view to gather the statistics.
Sample by
Specify the percentage of the table or indexed view, or the number of rows to sample when collecting statistics for larger tables or views.
Define the History Cleanup Task
On the Define History Cleanup Task page, define the database or databases where you want to discard old task history. This task uses the EXEC sp_purge_jobhistory, EXEC sp_maintplan_delete_log, and EXEC sp_delete_backuphistory statements to remove history information from the msdb tables. When finished, click Next.
The following options are available on this page.
Select the historical data to delete
Chose the type of task data to delete/
Backup and restore history
Retaining records of when recent backups were created can help SQL Server create a recovery plan when you want to restore a database. The retention period should be at least the frequency of full database backups.
SQL Server Agent Job history
This history can help you troubleshoot failed jobs, or determine why database actions occurred.
Maintenance Plan history
This history can help you troubleshoot failed maintenance plan jobs, or determine why database actions occurred.
Remove historical data older than
Specify age of items that you want to delete. You can specify Hour(s), Day(s), Week(s) (the default), Month(s), or Year(s)
Define the Execute Agent Job Task
On the Define Execute Agent Job Task page, under Available SQL Server Agent jobs, choose the job or jobs to run. This option will not be available if you have no SQL Agent jobs. This task uses the EXEC sp_start_job statement. For more information, see sp_start_job (Transact-SQL)When finished, click Next.
Define Backup Tasks
1. On the Define Backup Database (Full) Task page, select the database or databases on which to run a full backup. This task uses the BACKUP DATABASE statement. For more information, see BACKUP (Transact-SQL). When finished, click Next.
Copyright ?2012 Open Systems Holdings Corp. All rights reserved. TRAVERSE is a registered trademark of Open Systems Holdings Corp. All other marks are the property of their respective holders.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- listing 1 1 implementing column level encryption using a
- infor sunsystems installation guide
- session 4 3 sage 100 contractor database administration
- idaho technology authority ita enterprise guidelines
- manage comprehensive security best practice guide
- managing and understanding mas500 user accounts with sage
- cohesity dataplatform protecting individual ms sql
- configuration guide for sql server lepide
- user manual circutor
- configuring heterogeneous replication server for mssql azure
Related searches
- why use the internet
- why was the savings bond wizard discontinued
- how people use the internet
- how we use the internet
- teaching kids how to use the bible
- reasons to use the internet
- why people use the internet
- never use the word very
- what companies use the work number
- companies that use the work number
- employers that use the work number
- why use the writing process