Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is SQL Server database contraction". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is SQL Server database contraction".

1. Why shrink the database?

The SQL Server database adopts the method of pre-allocating space to establish the data file or log file of the database, for example, the space of the data file allocates 300MB, but actually occupies only 20MB space, which will result in a waste of disk storage space. Each file in the database can be shrunk by database shrinkage technology to delete pages that have been allocated but not in use. So as to save the cost of storage on the server.

2. The principle of database contraction.

Official explanation: shrink the data file to recover space by moving the data page from the end of the file to unoccupied space closer to the beginning of the file. After you create enough free space at the end of the file, you can unallocate the data pages at the end of the file and return them to the file system.

3. Limitations and limitations of database shrinkage

The shrunk database cannot be smaller than the size specified when the database was originally created. Or the last explicit size set using a file size change operation, such as DBCC SHRINKFILE.

For example, if the database was originally created with a size of 10 MB and then grew to 100 MB, the database can only shrink to a minimum of 10 MB, even if all data in the database has been deleted.

You cannot shrink the database when backing up the database. Conversely, you cannot back up the database when it performs a shrink operation.

4. The way database shrinks 4.1 shrinks database DBCC SHRINKDATABASE

Description: shrink the size of the data file in the specified database.

Syntax format:

DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY}])

Parameter description:

Database_name: is the name of the database to shrink

Target_percent: is the percentage of the remaining free space that you want in the database file after the database is shrunk.

NOTRUNCATE: causes the freed file space to be reserved in the database file. If not specified, the freed filespace is released to the operating system.

TRUNCATEONLY: causes any unused space in the data file to be released to the operating system and the file shrinks to the last allocated size, thereby reducing the file size without moving any data. Do not attempt to relocate rows of unallocated pages. Ignore target_percentis when using TRUNCATEONLY.

4.2 shrink the database file DBCC SHRINKFILE

Description: shrink the size of the specified data or log file in the current database, or empty the file by moving the data from the specified file to another file in the same filegroup to allow the file to be deleted from the database. The file size can be shrunk to a smaller size than the size specified when the file was created. This resets the minimum file size to the new value.

Syntax format:

DBCC SHRINKFILE ({file_name | file_id} {[, EMPTYFILE] | [, target_size] [, {NOTRUNCATE | TRUNCATEONLY}]]}) [WITH NO_INFOMSGS]

Parameter description:

File_name: the logical name of the file to shrink.

File_id: the ID number of the file to be shrunk. To get the file ID, use the FILE_IDEX system function, or query the sys.database_files catalog view in the current database

Target_size: the file size in megabytes (expressed as integers). If not specified, DBCC SHRINKFILE reduces the file size to the default file size. The default size is the size specified when the file was created.

Note: you can use DBCC SHRINKFILE target_size to reduce the default size of empty files.

For example, if you create a file for 10MB, and then shrink the file to 2 MB while the file is still empty, the default file size will be set to 2 MB. This applies only to empty files that will never contain data.

EMPTYFILE: migrates all data in the specified file to other files in the same filegroup. Because the database engine no longer allows data to be placed in an empty file, you can use the ALTER DATABASE statement to delete the file.

NOTRUNCATE: moves the allocated page from the end of the data file to the unallocated page in front of the file with or without specifying a target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when you specify NOTRUNCATE, the file does not appear to shrink.

NOTRUNCATE applies only to data files. Log files are not affected.

TRUNCATEONLY: frees up all available space at the end of the file to the operating system, but does not perform any page moves within the file. The data file shrinks only to the last allocated area. If target_size is specified with TRUNCATEONLY, this parameter is ignored. TRUNCATEONLY applies only to data files.

WITH NO_INFOMSGS: cancels the display of all informational messages.

5. Example-shrink the size of the TestDB file in the TestDB database to 20MB. USE TestDB; GO DBCC SHRINKFILE (TestDB, 20);-- will reduce the size of data files and log files in the UserDB user database to leave 10% free space in the database DBCC SHRINKDATABASE (TestDB, 30) -- Clean the database log file to 2m USE master ALTER DATABASE TestDB SET RECOVERY SIMPLE WITH NO_WAIT ALTER DATABASE TestDB SET RECOVERY SIMPLE-- simple mode USE TestDB DBCC SHRINKFILE (NumberTestDB log', 2, TRUNCATEONLY)-- set the compressed log size to 2m, and you can specify USE master ALTER DATABASE TestDB SET RECOVERY FULL WITH NO_WAIT ALTER DATABASE TestDB SET RECOVERY FULL-- restore it to full mode. Thank you for reading. The above is the content of "what is SQL Server database contraction". After the study of this article, I believe you have a deeper understanding of what is SQL Server database contraction, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.