Data files and log files shrink SQL Server

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