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          - '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)

Only one of the two truncate options can be specified - they do not apply to log files.

Examples

Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);

Shrink a Log file to 4 Mb (in Simple recovery mode)

USE MyDatabase;
GO
-- Set database recovery model to SIMPLE.
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 4 MB.
DBCC SHRINKFILE (MyDatabase_Log, 4);
GO
-- Set database recovery model to FULL.
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:

ALTER DATABASE
DBCC SHRINKDATABASE
FILE_ID
sys.database_files

Equivalent Oracle command:

ALTER DATABASE Datafile '/opt/oracle/oradata/myserver/ss64.dbf' resize 500M;