What is the use of SQL Server to compress data files?

Source: Internet
Author: User
What is the use of SQL Server to compress data files?

Prelude:

The other day, due to customer problems (in fact, piracy issues), you can only use the free sqlserver Express version.

The data file size of the entire database of the Express version of sqlserver is limited to 4 GB, that is, no matter how many file groups you use, how many auxiliary data files NDF

The total capacity cannot exceed 4 GB (MDF + NDF)

There is no limit on the size of the transaction log file

Because our database only uses a master data file gpos. MDF and a transaction log file gpos. LDF

 

My solution:

I am thinking that if this is the case, it will shrink the database.

I checked the information online:Because DBCC shrinkdatabase affects all files (including data files and log files) at the same time during one operation, you cannot

Specify the target size of each file, and the results may not meet the expected requirements. Therefore, we recommend that you first plan and determine the expected target for each file, and then use DBCC shrinkfile

It is safer to use one file and one file.

I was very happy to say that I used DBCC shrinkfile to compress files on the Internet, so I am not afraid to pull (I am not afraid to pull ~)

However, let's look at the information:

1. First, understand the current usage of data files

The size of the contraction cannot exceed the free space of the current file. If you want to compress the size of the database, first make sure that the data file does not have the space to be used. If

In use, it is necessary to confirm the objects (tables or indexes) occupying a large amount of space ). Then, archive the historical data and release the space first.

 

2. The primary data file cannot be cleared. Only auxiliary data files that can be completely cleared

 

3. If you want to clear a file group, delete the objects (tables or indexes) allocated to the file group or move them to another file group.
DBCC shrinkfile won't help you do this.

After clearing the data and objects in the data file and confirming that the data file (Group) has enough free space, the administrator can use DBCC shrinkfile to narrow down or clear the specified file.

To reduce the file size, enter the required target_size. To clear the file, select emptyfile.

 

According to the above information, my solution is as follows:

1. Confirm the objects (tables or indexes) that occupy a large amount of space ). Then, archive historical data, release the space, and compress the data file.

2. Re-index the data page and re-arrange it once. The original page is released, and the Occupied partitions are also released, and then DBCC shrinkfile

If you have other solutions, please let me know. Thank you !!

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.