Use [database shrinking function] to merge multiple data files

Shrink user database

 

1. Why do we need to shrink the database?

SQL Server 2000 uses the pre-allocated space method to create database data files or log files. For example, the data file space is allocated 100 MB, but actually only occupies 50 MB, this will cause a waste of storage space. To this end, SQL Server 2000 provides the function of shrinking the database, allowing you to contract each file in the database and delete the allocated but not used pages.

The entire database cannot be reduced to a smaller size than its original size. Therefore, if the size of the database is 10 MB and later increases to 100 MB, the minimum size of the database can be reduced to 10 MB (assuming that all data in the database has been deleted ). You cannot contract the database when backing up the database, or create or back up the database when shrinking the database.

2. What is automatic and manual contraction?

SQL Server 2000 supports automatic and manual database contraction. On the "options" tab of the database properties shown in 4.38, select the "auto contract" check box to enable auto contract. The SQL Server 2000 Server checks the database space usage every 30 minutes. If a large amount of idle space is found, the database file size will be automatically reduced.

Figure 4.38 set the auto-shrinking Function

You can use the Enterprise Manager, dbcc shrinkdatabase, and dbcc shrinkfile statements to perform manual contraction.

3. What is the difference between shrinking data files and log files?

Whether it is to shrink data files or log files, it will indeed reduce the size of physical files. The differences between the two are as follows.

Data files can be manually shrunk as file groups or separately. Every file is considered as the log file shrinkage, which is based on the entire log file. Shrinking log files will delete the inactive VLF.

4. How to perform the contraction operation?

The file contraction operation always starts from the end of the file. For example, if you want to contract a 5 GB file to 4 GB, SQL Server 2000 will release as much space as possible from the last 1 GB of the file. If the released part of the file contains pages that have been used, SQL Server 2000 relocates these pages to the reserved part.

Only the database can be reduced to no available space. For example, a 5 GB database already has 4 GB of data. If you specify to shrink the database to 3 GB, it will actually only release 1 GB of space.

If the scale-in operation cannot contract all the space in the log file, the system will feed back information, indicating what operations must be performed to release more space that meets the conditions.

5. Database contraction case

(1) Right-click the created database mydatabase In the manage targets navigation tree of Enterprise Manager, select [all tasks]/[shrink database] In the shortcut menu that appears.

(2) The "shrink Database" Page shown in "4.39" is displayed.

Q in the database size area, you can view [allocated space] and [available space]. This is an important basis for determining whether to scale down.

Q: In the "scale down operation" area, set the maximum available space in the "scale down file" text box to the maximum available space percentage in the data file after the database is scaled down. Select move page to file start position before shrinking to specify the start position of the page to file before shrinking the database. Selecting this option may affect performance.

Q in the scheduling area, select the check box and click the button to set the time for automatically shrinking the database.

Figure 4.39 "shrink Database" Page figure 4.40 "contract file" Page

(3) Click the "contract file" button in the "contract file" Area in Figure 4.39 to display the "contract file" interface shown in Figure 4.40. You can set a more precise contraction policy for data files or log files. In the drop-down list box of [database files], you can select the data file or log file to be shrunk. In the [contraction operation] area, you can set the specific contraction operation of the file, including whether to cut off the page, compress the page, and the size of the compressed file. In the latency reduction area, you can set the automatic contraction time. After completing the settings, click the button.

By shrinking the database, you can delete allocated but unused pages to make better use of the storage space. What corresponds to the contraction is to expand the database, that is, to modify the database attributes. A certain amount of free space should be available after the contraction, so that no additional space is allocated when the data changes.

 

6. Use the shrink function to merge multiple data files

During the contraction, select the data file to be shrunk and select the 'clear file' item to merge the data in the data file into the remaining data file, if you select a log file, select 'Contract file to 'and enter 0 m. In this way, these files can be merged. After that, although the data of the merged files has already been stored in a data file, the original data file still exists and cannot be deleted directly, you need to delete the data file and transaction log under the 'properties' option. For example: