Shrink Database DBCC Shrinkfile

Each file in the database can be reduced by removing unused pages. Although the database engine is effectively re-using space, shrinking a file becomes necessary after multiple occurrences of a file without the original size. Both the data and transaction log files can be reduced (shrunk). You can manually shrink the database files either in groups or individually, or you can set the database to shrink automatically at a specified interval.

The file always shrinks from the end. For example, if you have a 5 GB file and you specify target_size as 4 GB in the DBCC SHRINKFILE statement, the database engine frees as much space as possible from the last 1 GB of the file. If the part of the file that is freed contains pages that have been used, the database engine first places the pages back into the reserved portion of the file. You can only shrink the database until there is no remaining free space. For example, if a 5 GB database has 4 GB of data, and you specify target_size as 3 GB in the DBCC SHRINKFILE statement, you can only release 1 GB.

Operation Command:

Now I have a library Asfcore log file has reached 30G, the data file is 1G, if you want to shrink the log file, execute the following command!

• To view the RECOVERY_MODEL_DESC type of a database

SELECT NAME, Recovery_model_desc from sys.databases

• If it is full type, modify to Simple Type

ALTER DATABASE Asfcore SET Recovery Simple

• Shrink log file size (in units of M)

DBCC shrinkfile (N ' Asfcore_log ', 10)

• Check the log file name

Use Asfcore
SELECT NAME, size from sys.database_files

• Revert to full type

ALTER DATABASE asfcore SET Recovery full

Shrink Database DBCC Shrinkfile