Shrink the size of the current database data / log file or empty a file by moving the data.
Syntax DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS] DBCC SHRINKFILE ( file , target_size [, {NOTRUNCATE | TRUNCATEONLY }] ) [WITH NO_INFOMSGS ] Key: file - Logical file name or file_id EMPTYFILE - Migrate data to other files in the same filegroup. The file can be removed with ALTER DATABASE. target_size - The size for the file in megabytes. default = that specified when the file was created, or the last size used with ALTER DATABASE.(int) NOTRUNCATE - Free space at the end of the data file is not returned to the OS (pages are still moved) TRUNCATEONLY - Release free space at the end of the data file to the OS (do not move pages) NO_INFOMSGS - Suppress all information messages (severity 0-10)
You can shrink a transaction log file while the system is in use (DML commands are also being executed), however this will only affect the inactive portion of the transaction log file.
Discover the file_ID for each file with the SQL: SELECT file_id, name FROM sys.database_files;
After using TRUNCATE_ONLY you must perform a full backup
Examples
Shrink a datafile to 64 Mb:
DBCC SHRINKFILE (MyDataFile01, 64);
Shrink a Log file to 64 Mb:
USE MyDatabase; GO DBCC SHRINKFILE(MyDatabase_Log, 64) BACKUP LOG MyDatabase WITH TRUNCATE_ONLY DBCC SHRINKFILE(MyDatabase_Log, 64)
Afterwords, perform a full backup of the database.
To make the file as small as possible you can specify 1 for 1 Mb, or just leave out the target_size completely, be aware that doing this will slow down the system a little as the system will just have to grow the log file again as soon as another transaction is started.
Set database recovery model to SIMPLE or FULL
ALTER DATABASE MyDatabase SET RECOVERY FULL; GO
No Comment