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.
[sql]
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO
[/sql]
B. Shrinking a log file to a specified target size
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.
[sql]
USE AdventureWorks2012;
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO
[/sql]
C. Truncating a data file
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.
[sql]
USE AdventureWorks2012;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);
[/sql]
D. Emptying a file
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.
[sql]
USE AdventureWorks2012;
GO
— Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2012
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 AdventureWorks2012
REMOVE FILE Test1data;
GO
[/sql]
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql
Home