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 8 GiB (8192 MiB):
USE MyDatabase; GO DBCC SHRINKFILE(MyDatabase_Log, 8192)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_Log, 8192)
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.
In SQL Server 2008 the procedure is slightly different, the database must first be set to Simple recovery mode, then shrink the file, then restore FULL recovery mode:
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE go DBCC SHRINKFILE(MyDatabase_log) go EXEC sp_helpdb MyDatabase go ALTER DATABASE MyDatabase SET RECOVERY FULL go
“Men shrink less from offending one who inspires love than one who inspires fear” ~ Niccolo Machiavelli
Related:
Why you want to be restrictive with shrink of database files - karaszi.com
ALTER DATABASE MyDatabase SET RECOVERY FULL;
DBCC SHRINKDATABASE
FILE_ID
sys.database_files
Equivalent Oracle command: ALTER DATABASE Datafile '/oradata/ss64.dbf' resize 64M;