Database file Management->> Shrink Data file

Today in the development environment encountered a problem, we found that the hard disk space on the server is full, look at the next found that this disk storage database file should be the source of a not very large Coucay pair. After the inspection, it was found that a data file under this database accounted for 70% of the space under the drive letter, and most of the data came from a table. But even if I delete all the data in the table, the data file doesn't give the space back to the operating system. Then face a problem of how to shrink the file size. The first thing I think about is the DBCC SHRINKFILE command under SQL Server, which makes it possible to shrink the size of a database file by executing two DBCC SHRINKFILE commands in a row.

The first command to the file at the end of the page as far as possible to the top of the file on the free page, this applies to the data file and not to the log file; the second command truncates the blank page at the end of the file and gives it back to the operating system. It looks as if you can achieve your goal. However, these two commands run too slowly. First of all, this file is 100GB in size, with only one-tenth of the space occupied by the page is 10GB. The second command took me almost 3 hours to finish the run. It is also important that the second command will cause blocking phenomena. During the shrinkfile process, a colleague cannot access the data stored in the file in a table.

Use [Yourdb]
GO

DBCC shrinkfile (notruncate);
DBCC shrinkfile (truncateonly);

So I thought of another way, that is to put the data into another disk under the file (the same filegroup), and then delete the file, ALTER DATABASE to modify the path of the file, the file path is set to the original drive file, and then set the database Offline, copy the file back to the original drive letter and set the database online. That's it. This approach applies to the actual page of the file itself is very small, such as my example, 10%, which is very good solution. As for the last approach, there is time to experiment whether it is set up.

Database file Management->> Shrink Data file