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

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 !!