Shrink database DBCC shrinkfile

 

Each file in the database can be reduced by deleting unused pages. Although the database engine will effectively reuse the space, it is necessary to shrink a file multiple times without the original size. Data and transaction log files can be reduced (shrunk ). You can manually contract database files in groups or separately, or set the database to automatically contract at specified intervals.

The file is always shrunk from the end. For example, if there is a 5 GB file, and in the DBCC shrinkfile statementTarget_sizeIf it is 4 GB, the database engine will release as much space as possible from the last 1 GB of the file. If the released part of the file contains used pages, the database engine first places these pages to the reserved part of the file. Only the database can be reduced to no available space. For example, if a 5 GB database has 4 GB DataTarget_sizeIf it is 3 GB, only 1 GB can be released.

 

Operation Command:

Now the log file of an asfcore library has reached 30 GB, and the data file is 1 GB. to contract the log file, run the following command!

• View the recovery_model_desc type of the database

Select name, recovery_model_desc from SYS. Databases

• For full type, change to simple type

Alter database asfcore set recovery simple

• Shrink log file size (unit: m)

DBCC shrinkfile (n'asfcore _ log', 10)

• Check log file name

Use asfcore
Select name, size from SYS. database_files

• Restore to full type

Alter database asfcore set recovery full