A. Shrinking a data file to a specified target size
The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.
Copy
Use UserDB; GODBCC Shrinkfile (DataFile1, 7); GO
B. Shrinking a log file to a specified target size
The following example shrinks the log file in the ADVENTUREWORKS2008R2 database to 1 MB. To allow the DBCC shrinkfile command to shrink a file, you first need to truncate the file by setting the database recovery mode to simple.
Copy
Use ADVENTUREWORKS2008R2; go--Truncate The log by changing the database recovery model to simple. ALTER DATABASE adventureworks2008r2set RECOVERY simple; go--Shrink The truncated log file to 1 MB. DBCC Shrinkfile (Adventureworks2008r2_log, 1); go--Reset the database recovery model. ALTER DATABASE adventureworks2008r2set RECOVERY full; GO
C. truncating a data file
The following example truncates the master data file in the ADVENTUREWORKS2008R2 database. You need to query the Sys.database_files catalog view to get the file_id of the data file .
Copy
Use ADVENTUREWORKS2008R2; Goselect file_id, Namefrom sys.database_files; GODBCC shrinkfile (1, truncateonly);
D. Emptying the file
The following example shows the steps to empty a file to remove it from the database. For this example, you first create a data file and assume that the file contains data.
Copy
Use ADVENTUREWORKS2008R2; go--Create A data file and assume it contains data. ALTER DATABASE adventureworks2008r2 ADD FILE ( NAME = test1data, FILENAME = ' c:/t1data.ndf ', SIZE = 5MB ) ; go--Empty the data file. DBCC shrinkfile (Test1data, emptyfile); go--Remove The data file from the database. ALTER DATABASE adventureworks2008r2remove FILE test1data; GO
Data files and log files shrink SQL Server