The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.<\/p>\n
[sql]<\/p>\n
USE UserDB;
\nGO
\nDBCC SHRINKFILE (DataFile1, 7);
\nGO<\/p>\n
[\/sql]<\/p>\n
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.<\/p>\n
[sql]<\/p>\n
USE AdventureWorks2012;
\nGO
\n— Truncate the log by changing the database recovery model to SIMPLE.
\nALTER DATABASE AdventureWorks2012
\nSET RECOVERY SIMPLE;
\nGO
\n— Shrink the truncated log file to 1 MB.
\nDBCC SHRINKFILE (AdventureWorks2012_Log, 1);
\nGO
\n— Reset the database recovery model.
\nALTER DATABASE AdventureWorks2012
\nSET RECOVERY FULL;
\nGO<\/p>\n
[\/sql]<\/p>\n
The following example truncates the primary data file in the AdventureWorks database. The sys.database_files catalog view is queried to obtain the file_id of the data file.<\/p>\n
[sql]<\/p>\n
<\/p>\n
USE AdventureWorks2012;
\nGO
\nSELECT file_id, name
\nFROM sys.database_files;
\nGO
\nDBCC SHRINKFILE (1, TRUNCATEONLY);<\/p>\n
[\/sql]<\/p>\n
The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.<\/p>\n
[sql]<\/p>\n
USE AdventureWorks2012;
\nGO
\n— Create a data file and assume it contains data.
\nALTER DATABASE AdventureWorks2012
\nADD FILE (
\nNAME = Test1data,
\nFILENAME = ‘C:\\t1data.ndf’,
\nSIZE = 5MB
\n);
\nGO
\n— Empty the data file.
\nDBCC SHRINKFILE (Test1data, EMPTYFILE);
\nGO
\n— Remove the data file from the database.
\nALTER DATABASE AdventureWorks2012
\nREMOVE FILE Test1data;
\nGO<\/p>\n
[\/sql]<\/p>\n