30. Microsoft SQL Server Management
SQL Server automatic setting function
Database maintenance plan
Summary of this chapter
Use the appropriate settings and perform routine maintenance tasks in the database, is the key to optimization of the server. This chapter will learn special features about dynamic settings in SQL Server 2000, which simplifies the program of setting the database. At the same time, we will also use the Database Maintenance PLAN Wizard to make the system automatic maintenance plan to keep the database in the optimal state.
SQL Server automatically sets the functionality of SQL Server to reducing loads related to setting and regulating the associated database management system (RDBMS). Since these functions are developed from SQL Server 7.0, users of SQL Server 7.0 should be unfamiliar with these features. This chapter will learn these functions, how to use them to reduce the workload of the database, and eliminate automation functionality when necessary. Dynamic Memory Management Memory Management (DynamiMory Management) allows SQL Server to allocate memory size based on the size of the system so that dynamic management buffer fast partition regions and programs. Due to the SQL Server dynamic memory management function, DBA does not have to manually control the size of the quick-strip. However, in some cases, it may be necessary to limit the memory size that SQL Server can use. Dynamic memory management operation dynamic memory management is constantly monitoring the size of the available entity memory in the system to achieve management. SQL Server determines the increase or decreases of the SQL Server Memory Shared Area (described below) as needed and the amount of memory can be used. This feature is very practical when the memory size is fixed; however, when the memory is used to handle other programs other than SQL Server in the system, it will cause problems due to the constant memory configuration of the memory because SQL Server constantly changes the configuration of the memory. If the system of a computer is mainly used as a SQL Server library server, it is ideal for dynamic memory management. In such systems, the memory used in the SQL Server processing range is more fixed, so the SQL Server will continue to automatically configure the memory, so that the work is efficient, such a configuration will continue until No more physical memory is available. If the existing handler does not require more memories, the system will remain in such a state. If another program needs to be used to use the memory, SQL Server will disseminate the memory size required for the new program, and configure the new program smoothly. The computer system used to handle the job program is not suitable for use dynamic memory. Due to the continuous change of the demand of memory, the handler must often increase the memory. Under such systems, the amount of memory is often changed, and SQL Server must constantly set and revoke the configuration of the memory, increase the burden of the system, and lack the efficiency of the processing process. If you can manually configure a fixed memory size in SQL Server, or the maximum maximum memory capacity configurable for SQL Server can make this type of system to operate better, and later in this chapter will be more Detailed explanation. Whether it is dynamic or manual to manage memory, the system is good or bad to see if the memory of memory can play the most effect. Through the memory configuration of monitoring SQL Server, it is possible to determine if the amount of use of the memory is fixed, or remains in a stable state. The performance monitor (Performance Monitor) using Windows 2000 can monitor the memory, such as the Total Server Memory (KB Ventive Counter) in SQL Server. The memory administrator (Memory Manager) displays the most recently consumed memory size (KB) of SQL Server. Memory Shared Area SQL Server is dynamically configured and revoked in the centralized center. The memory shared area consists of the memory of the following components: the data page that has been read in the memory of the memory quickly, and the buffer fast partition regions usually occupy most memory shared regions. Connect the memory with the connection point in each SQL Server.
The connection memory is composed of the data structure that can continuously track the contents of each user, contains the cursor location information, query parameter value, and pre-deposit information. The data structure contains domain information about locking and database descriptions, including information about locking owners, locked, and various files and archives. Record Express Area is used to save transaction information written in a transaction record, and also uses record information to read the currently written record. Recording Express Area enhances the performance of records, and the buffer fast section is different.
The program snapper is used to store Transact-SQL statement execution plans and save all steps when executed. Because the memory configuration is dynamic, if the memory common area can be continuously increased or decreased in dynamic memory management, the five regions of the memory shared area can also dynamically change the respective sizes. This feature is controlled by SQL Server. For example, if there is more demand for memory, there will be more T-SQL statements being stored in the program. SQL Server will take some memories to the program. District use. Use additional memory SQL Server can access the memory size to see the usage of the Windows job system. Windows NT Server 4 supports 4GB memory, where 2GB is used in the user program, and the additional 2GB leaves system. This means that in NT 4, the SQL Server configurable memory limit is within 2GB. However, in Windows NT Server 4 Enterprise Edition, the virtual memory space configured for each program process is 50% (3GB). This expansion can be made because the configuration of the system itself is reduced to 1GB. The virtual memory used in the program is increased, so the size of the memory shared area can be expanded to nearly 3GB. To use the support in the Windows NT 4 Enterprise Edition, you must join / 3GB tags in the Boot.ini file launch data line, which can be done through the system illustration of the console. For two versions of the Windows 2000 job system, SQL Server 2000 Enterprise Edition can provide greater memory space with Windows 2000 Address Windowing Extensions (AWE) API. Nearly 8GB supports near Windows 2000 High-Order Servo, and supports nearly 64GB of memory in Windows 2000 Datacenter Server. AWE only supports these two operating systems and cannot support Windows 2000 Professional. (For details, please refer to the "AWE Memory in Windows 2000" in Windows 2000.) Memory Settings Options.) The SQL Server configuration settings are related to the memory configuration. The configuration can be set by SQL Server Enterprise Manager or SP_Configure pre-deposit program. To view these configurations with sp_configure, you must set the option of show Advanced Options into 1. AWE enabled allows SQL Server to extend memory (previously mentioned AWE memories). This option can only be used in the SQL Server Enterprise Edition and can only be set with sp_configure. INDEX CREATE MEMORY is limited to memory size sorting during index establishment. The Index Create Memory option is self-designed, and it does not need to be adjusted in most cases. But if you have difficulty in establishing an index, you may try to increase the value of the option on the preset value. Max Server Memory Sets SQL Server to the Maximum Memory Space of Memory Shared Area. If SQL Server is dynamically configured and revoked, leave a preset value setting; if you want to configure the memory (so space is not used), you can set this option and the Min Server Memory to the same value. MIN MEMORY PER Query Specifies the Minimum Memory Space (KB) assigned to the execution query. MIN Server Memory Sets SQL Server can be assigned to the minimum memory space of the memory shared area, leaving a preset value to the dynamic memory configuration.
If you want to static memory, this option can be set to the same value as the Max Server Memory. SET WORKING SET SIZE Specifies the memory assigned by SQL Server to be unable to exchange, even if other programs can use memory more effectively. When SQL Server can dynamically configure memory, you should not use the SET WORKING SET SIZE option. It should only be used for MIN Server Memory and Max Server Memory to set the same value. In this way, SQL Server will assign a static memory size that cannot be changed. Explain the advantage of the memory option to be played, you must perform the SQL Server 2000 Enterprise Edition with the Windows 2000 Higher Order Servo Edition or Windows 2000 Data Center Servo Edition.
Other dynamic setting options Some dynamic setting options are not to belong to the server memory, can also be used in SQL Server. If the preset value of these options is retained, SQL Server will dynamically set all options. The preset value can overwrite, although this is not necessary, you should still know how these options operate when using manual settings. Setting options using SQL Server Enterprise Manager or sp_configure to set the option (not all options can be set through Enterprise Manager). Use the sp_configure setting option to open the Query Analyzer or OSQL connection in the command prompt window, and execute the pre-store with the parameter as follows:
sp_configure "option name", Value
Parameters Option Name is the name of the setting option, and Value is the value to be set. If the parameter value is not included when the command is executed, SQL Server will pass back to the current value currently the specified option. To see all the list of options and their values, perform sp_configure directly, no parameters are required. Some options are considered advanced options. In order to view and set these options with sp_configure, you must first set the show advanced options to 1, the syntax is as follows:
SP_Configure "Show Advanced Options", 1
The options set through Enterprise Manager are not affected by Show Advanced Options. In Enterprise Manager, select the right button after selecting the server, select the content into the SQL Server property (configuration) window in the quick function table, as shown in Figure 30-1.
Figure 30-1 General Tags of Property Windows in Enterprise Manager
Take a tab at the window to access certain dynamic options. Dynamic options that are independent of SQL and memory will be described in the next chapter and indicate if these options are set on Enterprise Manager, and where to find these options. Lock Option SQL Server Dynamically sets the size of the lock in the system in the system based on the current needs. You can set the maximum number of available quantities, limit the number of memorys that SQL Server uses on the lock. The preset is set to 0, which allows SQL Server to dynamically require allocation and revoking locks according to system requirements. SQL Server allows memory to be up to 40% to lock. Keep the LOCKS parameter is the preset value of 0 and allow SQL Server to be allocated to lock as needed. This option is the advancement option and can only be set with sp_configure. The restore interval option restore interval indicates the maximum number of minutes required for the SQL Server replying to repositories. (See "Automatic Checkpoint> section in this chapter.) SQL Server Reply Database Time to see when the last checkpoint occurs. Therefore, the value of recovery interval is SQL Server to dynamically decide when to perform automatic checkpoints. For example, each SQL Server is turned off, the checkpoint is executed in all databases, so when SQL Server is started again, only a little time recovery is required. However, if SQL Server is forced to stop (due to power interrupt or other mistake), SQL Server is started again, it will return to the trading that is not submitted to reply to the database, and do not write when SQL Server is still written. Transaction to the disk. If the final check point is executed before the system error in a specific database, the reply time of the database is shorter; if the final checkpoint is executed for a long time before the system mistakes, the reply time is longer. SQL Server determines how long the checkpoint is determined based on the built-in path and the restore interval. For example, if the recovery interval is set to 5, SQL Server will perform checkpoints at each database, so that the reply time after the error is about five minutes. The preset value of the restoration interval is 0, indicating that the SQL Server is automatically set. When using a preset value, the reply time is less than 1 minute, and the checkpoint is executed almost once per minute. In many cases, the performance of the checkpoint is frequently reduced. Therefore, you should increase the value of the restoration interval to reduce the number of checkpoints. The value you choose will depend on the needs of the company, and you can wait for how long the user can wait after the system errors. In general, the value of 5 to 15 is set (indicating that the reply time is 5 ~ 15 minutes). The restore interval option is advanced option. The properties window settings in Enterprise Manager can be set. Click on the database settings page sign, as shown in Figure 30-2. Enter a value in the restoration interval (minute) square. Figure 30-2 Setting the reply time
User Connection Option SQL Server Dynamically sets the number of users, allowing up to 32767 users to link. Through the setup connection user option, you can specify the maximum value that allows you to enter SQL Server (the number of users allowable users is also limited by the applications and hardware). The user connection can still be dynamically set to the maximum. For example, if only 10 users log in, 10 users are allocated. If you reach the maximum, SQL Server also needs more users to connect, there will be an error message, telling your user to connect the maximum. In most cases, there is no need to change the preset value of the wiring user option. Note that each connection is about 40 kB of memory. You can use SQL Server Query Analyzer, and the following T-SQL statements to determine your system allowed users' connection maximum: select @@ max_connections
The connection user option is the advancement option, which can be set in Enterprise Manager. Just select the connection tab in the server properties window and enter a value in the maximum fine tuning block at the same time, as shown in Figure 30-3.
Figure 30-3 Setting the user link
Opening the item Options The Open Objects option is the advancement option, you can only use the sp_configure setting, specify the number of larger database items, such as information sheets, view tables, pre-deposited programs, rules, and preset Value, you can start in the same time. Preset value 0 indicates that the system allows SQL Server to dynamically adjust the number of open objects. It is recommended to keep the preset value setting. If you have changed, and SQL Server needs to be more open than the setup, it will receive an error message, inform that exceeding the number of open objects allowed by the system. Also, each open object consumes some memories, so the system requires more entity memory to support the number of open objects. The statistical system requires information column statistics to increase query efficiency. SQL Server can collect statistics related to numerical distribution in the data sheet data column. Query Optimizer takes advantage of this information to determine the best implementation of the query. Statistics can be obtained in two data columns: part of the index and the induction of the query in the index. Leave the SQL Server set the database preset value, allow SQL Server to automatically build two types of statistics. Index column data and index are also established simultaneously, non-index column data is established when queries (only single data columns rather than multi-data columns, as explained later in Figure 30-4 Property Window of the Distribution Database In the property window of the database, you will also see an option for automatic update statistics. This option describes that SQL Server statistics will be updated automatically when necessary. These statistics must be updated when most of the numerical values in the data sheets, and through updates, inserts, or delete operations. When most data changes, the current statistics are not accurate. SQL Server will automatically decide when to update statistics. If you choose to remove this option to establish a statistics, you must use manual execution tasks to ensure that the database works. The following sections will tell you how to manually establish and update statistics. Establish a statistics You can use the T-SQL instruction Create Statistics to manually establish statistics in a specific information column of the data sheet. Manually establish statistics and automatic establishment. Manually allows you to combine the statistics of multiple data columns, produce average of the replication value and differential values. The syntax of Create Statistics is as follows: Create Statistics Stats_name on Table_name (Column [, Column ...]) [[$ [Fullscan | Sample size percent] [, NoreCompute] You must provide the statistical name, data sheet name, and at least one data column name. You can specify multiple data column names to collect statistics of the combined data column, but cannot specify the calculation information line or NTEXT, TEXT, or Image data type as a statistical data. Whether it is a full scan or individual sampling can be used as a statistical collection. Because the time to scan the information sheet per data, the full scan expensive time is more sampled than individual, but it will be more accurate. If you use individual sampling, you must specify the percentage of sample data. NORECompute specifies the automatic update capability of revocation, so that the statistics no longer represent the information. You may have to establish a statistics in the data column to use in the query. For example, you can establish statistics in the FirstName and Lastname data sheets of the Northwind Database Employees table, and use employees' somewhere and name to search employee data. The Usage of the T-SQL program is as follows: Create Statistics Name On northwind..employees (firstname, lastname) With fulscan, NoreCompute This statement calculates the statistics of all the data lines in the firstname and lastname data columns and revoke statistics. If all data sheets of the database and all the information columns are manually established, they don't want a column to type CREATE STATISTICS statements, and can use sp_createstats to prepare the program. The pre-depreciation program will be described below. SP_CREATESTATS You can establish statistics in all suitable information columns in the user's data sheet via pre-deposit program. Not yet statistical information columns will establish statistics. Each group of statistics will only be in a single data column. The syntax of sp_createstats is as follows: sp_createstats ['INDEXONLY'] [, 'Fullscan'] [, 'NoreCompute']] The indexonly parameter defines the only information column in the index can establish statistics. Fullscan parameters define that each data line will perform a full scan, not random sampling. NoreCompute parameter definition cannot be automatically updated on new statistics. New statistics are named with the original list of data established. Updating statistics SQL Server automatically updates statistics with preset values. You can use the update statistics command to revoke this option and use manual update statistics. This instruction allows the update index column and the non-index column statistics. You may have to create a UPDATE STATISTIC's instruction code, then regularly perform the instruction code, just like the work of SQL Server. This will help maintain the latest statistics and ensure better query efficiency. (See Chapter 17 Go SP_AUTOSTATS CUSTOMERS Go Output Displays the name of each statistic, whether the automatic update is set on or OFF, or when the recent update is. Don't be output from the first data to the index name title. It represents all statistics, not only indexes. If you don't need to turn off these statistics, they display the ON status, just like the preset value of SQL Server. To remove all statistics in the Northwind Database Customers Database, you must use the following instructions: Use northwind Go Sp_autostats Customers, 'Off' Go You can set the tag value to the ON to make the automatic statistics update again. Change statistics for specific statistics or indexes, including individual statistical names or index names. For example, the following instructions can set the automatic statistics update of the PK_CUSTOMERS index: Use nnorthwind Go Sp_autostats Customers, 'On', 'PK_CUSTOMERS' Go The status of all other statistics in the CUSTOMERS data sheet will not change. When you use SQL Server 2000, you can set your data files automatically grow. This feature is very easy to use because it prevents you from using the space. However, you can't monitor the size of the database because this feature is not monitored, or the capacity plan is performed occasionally. You should often pay attention to how fast the information sheet is growing. Then I can decide whether to delete unnecessary files regularly (perhaps the expiration data in some data sheets). In this way, you can slow down the growth of the data sheet. With the increase in the number of data in the information sheet, the query will spend more time and the effective level will drop. When establishing a database, set the title of automatic archive growth (as mentioned in Chapter 9), you will learn how to change the growth options for existing database. Automated file growth options can be set in Enterprise Manager. Follow the steps below: In the pane on the left of Enterprise Manager, expand a server and select the database folder. Press the right button on the database you want to modify (to modify the MYDB Database as an Example), select the content window of the database to enter the database. Click on the information archive page (as shown in Figure 30-5) to see the properties of the database data file. The options in the archive properties are used to control the growth mode of the data file. Let the files automatically grow, check the automatic growth of the cubes. If the file is allowed to grow, it should be limited to make the file will not grow unrestricted. Figure 30-5 MYDB Property Window Data Archive Tag Tag Use options in the Size Upper Limit to specify the upper limit of the file grows. Click to restrict the growth of the file and type the maximum value in the fine tuning block. If you do not restrict the growth of the file, you will find that the disk machine system is not carefully filled with information, resulting in performance and operational issues. The options in the archive growth area can define the speed of the archive growth. If you are selected in MB, once the data is filed, SQL Server will increase the size according to the specified value. If you select at a percentage, SQL Server will increase the size of the data file in accordance with the percentage of current size. Click on the trading record file page (as shown in Figure 30-6), set the automatic growth option of the transaction record. The usage method of these options is the same as the information archive tab, and it should also be limited to the transaction record file, and the file will not grow unlimited. Figure 30-6 MYDB Database Property Window Transaction Record Tape Tab Automatic archive growth features are very convenient in many cases, as long as you don't make the file accidentally consume all the system all the disk space. Checkpoint SQL Server automatically performs checkpoints. The frequency of the checkpoint will automatically calculate the value of the restoration interval in the specified SQL Server setting option. This option specifies that the system failure event occurs, the repository reply time (minute) you have to wait. The frequency of checkpoints must be sufficient to ensure that the system reply time is less than the specified number of minutes. When SQL Server is turned off in Shutdown statements or service control administrators, checkpoints will automatically appear. You can also manually set up checkpoints with Checkpoint statements. If you want the system to perform optimization, but also allow for a long reply time, the restore interval can be set to a large value, such as 60. This representative should spend 60 minutes to complete automatic reply. Checkpoints cause a large number of disk writes, which will take the resource from the user transaction, and thus slow down the user's response time. This is to implement fewer checkpoints that can often help improve all transaction efficacy. Of course, this value is too high. If the system is failed, it will lead to too long downtime. The general restoration interval is set between 5 and 15 minutes. The preset value of the restoration interval is 0. This setting allows SQL Server to determine the best time for performing checkpoints in accordance with the system load. Generally speaking, when using a preset value, the checkpoint is executed once a minute. If you noticed that the checkpoint often occurs, you may adjust the recovery interval setting. To determine if SQL Server is excessively executed checkpoint, use SQL Server tracking tag -t3502. This tag enables checkpoint information to be written in the SQL Server error record. Note that the checkpoint will appear in each database. The Database Maintenance Plan Maintenance Program is a set of tasks, SQL Server automatically performs in the database according to the scheduled schedule. The purpose of the maintenance plan is automatically operated important management tasks so that work will not be ignored and reduce the manual workload of DBA. You can establish individual programs for each database, establish multiple plans for a single database, or establish a single program for multiple libraries. When you establish a maintenance plan, you can schedule four main management tasks: optimization integrity Check full database backup transaction record backups Implementing these tasks is important for maintaining good performance and replied databases. The optimization task type you have to include depends on your database performance and usage. Performing integrity check is a good way to ensure a sound and consistent database. There is also a regular backup to ensure that the database can be replied to the system failure or user error. Because these backups are important, you should set up automatic backup policies. We will see more details of each task type later. Establish a maintenance plan with Database Maintenance Plan Wizard. In this chapter, you will learn how to use this elves, how to display work in the maintenance plan and how to edit the plan. Use the Database Maintenance Plan Wizard to establish a maintenance plan According to the following steps Perform the Database Maintenance Program Elf: Use the following way to start the Database Maintenance Program Elf: Select the Database Maintenance Plan in the Tool Form. Click on the left pane to select a database name and click the new maintenance plan under the maintenance header in the right pane. If you didn't see the maintenance title, check if you have selected your list in the viewing function table. Maybe it's going to scroll down the screen. Click on a database name to select the elves in the tool function table, expand the elf. The management data clips in the dialogue, then select the Database Maintenance Program Elf. Expand the server in the left pane, expand the management data clip, press the right button on the database maintenance plan, and select the new maintenance plan from the quick function table. Press the right button on the database name and select all work, and select the maintenance plan from the quick function table. Once you open the elves, you will see the welcome screen, as shown in Figure 30-7. Figure 30-7 Welcome screen of "Database Maintenance Program Elf" Select Next to enter the selection database screen, as shown in Figure 30-8. Choose one or more databases that need to establish a maintenance plan. Select Next to enter the update information optimization information screen, as shown in Figure 30-9. The optimization type can be selected for the database that has just been established: Re-organizing data and index pages This option improves the updated performance, removes the index of all information tables in the database in the database with a specific filling factor (or number of available space per page). This page does not require this page. For data sheets that are often inserted or updated, the initial free space in the index page will begin to fill, and the page also begins to split. Select this option to rebuild the index, re-establish the available space for future files, avoiding the delay and information caused by page splitting. Figure 30-8 Select the database screen You can choose to rebuild the index according to the original size of the available space, or you can specify a new to keep your blank page ratio. If the ratio you set is too high, you must take the risk of reading efficiency. If you choose this option, you can't select an update statistics ready by query optimizer option. Tip removal and reconstruction index will spend time than using DBCC DBREINDEX, as discussed in Chapter 17 Figure 30-9 Update information optimization information picture Updating the statistics used by the query optimization Select this option to make the SQL Server re-sampling all index distribution statistics in the database. It uses this information to select the best implementation plan for the query. If you do not have to change the preset option in order to update statistics (in front of this chapter), SQL Server automatically generates statistics, sampling with a smaller ratio of information corresponding to each index. This option can be used to force SQL Server to execute another sample, using a larger ratio specified, or how long the SQL Server should update statistics instead of applying it. The bigger the data sampling of the ratio, the more precise statistics, but SQL Server also spends more time to generate statistics. This information helps improve efficiency when the data in the index column is largely modified. You can use the SQL Server Query Analyzer to verify the execution plan of the query, determine if the index is used efficiently, determine if it is necessary to select this option. If this option is selected, you can't choose the option for REORGANIZE DATA AND INDEX PAGES. Removing the unused space from the database file can be used to remove unused space; this process is the file shrinkage of our knowledge (File Shrink). You can specify how much unused space should be compressed, as well as how many ratios should be retained after compression. Once the available space is removed, you can use DBCC ShrinkFile to reduce the size of the file. If you need it, you can make it smaller than just established. This allows the disk space occupied by the file available for other needs. Also, the removal of the compressed data that does not use space can improve efficiency. In the only reading data sheet, compression is unnecessary. You can select the change button to enter the editing duplicate job schedule, specify the execution time of the task, as shown in Figure 30-10. These tasks can be executed lower in system usage (such as weekends or evening), because they need to spend more time, may delay the time of responding to the user. Select Next to enter the database integrity check screen, as shown in Figure 30-11. In this picture, you can choose whether to perform an integrity check. Integrity checks will perform DBCC CHECKDB instructions to verify the configuration and structural integrity of the data sheet and the index (if you select an index option). You can choose whether to include indexes, whether SQL Server should fix the discovered small problem (recommended to select this option), and whether all integrity checks should be performed before the backup. If you choose to perform an inspection before the backup and find a problem, the backup will not be performed. Click Change to change the execution time of these tasks. Integrity checks may take several hours to perform, depending on your database size. Therefore, it is necessary to determine how it will be arranged in the database usage. The check should be performed regularly, perhaps once a week or month, or before the database backup. Figure 30-10 Editing the repeated execution job schedule dialog Figure 30-11 Database integrity check screen Select Next to enter the specified database backup plan screen, as shown in Figure 30-12. This screen can choose whether to establish a self-automatic backup plan (recommended to use this plan). Check that the database backup as a maintenance plan can create automatic backup (Chapter 32 for detailed description of backup). You can instruct SQL Server to test the integrity of the backup when completing. SQL Server will determine that the backup is complete, and all backup content can be accessed. You can also indicate whether the backup must be stored on a tape or disk. Click Change to change the execution time of the backup. Select Next to enter the specified backup disk directory screen, as shown in Figure 30-13. This picture will only appear when you specify back up to the disk in the last screen. If you specify back up to tape, the screen will not appear. Here you can specify the location of the backup file, or you can use the preset backup directory. If the not only a database (such as Master, Model, MSDB) to be backed up, put each database backup in its sub-directory to allow the backup file to systematically. You can choose to automatically delete backup files for a certain period of time to move the disk space and you can specify the secondary file name of the backup file. Figure 30-12 Specifying the database backup plan screen Figure 30-13 Specifying a backup disk catalog screen Select Next to enter the designated transaction record file backup plan, as shown in Figure 30-14. This screen is similar to the specified database backup plan screen of Figure 30-12, but the options for this screen are used to establish a backup transaction record. Transaction record backups should be performed between the database backup. To reply to any changes from the last database backup, you can use the transaction record backup. In other words, the transaction record backup lets you reply to the data between the database backup. Figure 30-14 Specify transaction record file backup plan screen If you choose to store backups on a disk, the next screen you will see is a specified transaction record file backup disk directory, and you can provide backup file location information in this screen. Select Next to enter the generating report screen, as shown in Figure 30-15. This picture provides the option to establish a report, including the results of the maintenance plan task. In this picture, you can also select the location of the report, delete a report that exceeds a certain date, and you can transfer this report to the specified address by email. Select Next to enter the maintenance plan history record screen (Figure 30-16). Here you can choose whether or not to write to the database data sheet for this server server, you can also set the maximum capacity of the report. You can also write a report to a remote server and specify the maximum capacity of the report. Figure 30-15 Generate a report picture Figure 30-16 Maintenance Plan History Recording Screen Select Next to enter the completion of the database maintenance wizard, as shown in Figure 30-17. This picture shows the summary of the maintenance plan. This plan has a preset name, but you can also type the specified name in the text block of the planned name. Check out the summary, if you want to change any option, you can go back to modify. If the plan is correct, you will choose to complete. Figure 30-17 Completing the database maintenance wizard picture In the maintenance plan examples, we have established a task in four types. To see a list of work lists or scheduled tasks, enable the management folder in the left pane of Enterprise Manager, expand the SQL Server Agent, and select the job, as shown in Figure 30-18. Figure 30-18 Work established with maintenance plan Editing Maintenance Program To edit the maintenance plan, first select the established database name on the left pane of Enterprise Manager, then select the plan name under the right pane (perhaps you must scroll down the title) The dialogue of the Database Maintenance Program will appear, as shown in Figure 30-19. After completing the information, the general pagemark allows the specified data library to be applied to the maintenance plan. Other tabs can change the settings in the original database maintenance plan. Click OK when you complete the plan to modify, and the maintenance plan will start immediately according to the new arrangement. Note You must let SQL Server Agent begin with the progress of the automatic maintenance plan. See Chapter 31 for details. Figure 30-19 "General" page sign in the "Database Maintenance Scheme" dialogue This chapter summarizes in this chapter, you have learned the dynamic setting of SQL2000, which helps you reduce the usage of the database when performing DBA. You also learned to make a database maintenance plan, you can automate administrative tasks. The next chapter will tell you how to use SQL Agent to define work and warnings. With the setting work and warning, you can make the automation of the management task further.
