DBCC SHRINKFILE

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 commands

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;


 
Copyright © 1999-2024 SS64.com
Some rights reserved