In SQL Server, when using Transact-SQL, reducing the size of a data file requires a different syntax to increasing it. To reduce a file size using T-SQL, use the DBCC SHRINKFILE command, and provide the name of the data file along with the size you’d like to reduce it to.

Below are examples of reducing the file size of a data file in SQL Server using T-SQL.

Basic Example

Here’s a basic example of reducing a data file’s size using T-SQL:

USE Solutions;
GO

DBCC SHRINKFILE (Solutions_dat_2, 5);
GO

This is probably quite self-explanatory. First, we switch over to the database whose data file we want to change. We then use DBCC SHRINKFILE to specify which data file, and what file size to reduce it to. This is in MB, so our example will reduce it to 5MB.

Reduce it to the Default File Size

If you don’t specify a file size, the file will be reduced to its default size. The default size is the size specified when the file was created.

Example:

USE Solutions;
GO

DBCC SHRINKFILE (Solutions_dat_2);
GO

Truncate the Data File

You can truncate the data file by using the TRUNCATEONLY argument.

Here’s an example:

USE Solutions;  
GO  
DBCC SHRINKFILE ('Solutions', TRUNCATEONLY);
GO

Empty a Data File

You can also empty a data file by using the EMPTYFILE argument.

Example:

DBCC SHRINKFILE (Solutions2, EMPTYFILE);  
GO 

This migrates all data from the specified file to other files in the same filegroup. Therefore, you’ll need to make sure this isn’t the only file in the filegroup (otherwise you’ll get an error). EmptyFile also assures you that no new data will be added to the file.

Once you’ve emptied the file, you can remove it if that’s what you want to do. Here’s an example of removing the emptied file:

ALTER DATABASE Solutions  
REMOVE FILE Solutions2;  
GO 

And we can check that it has been removed by running the following:

USE Solutions;  
GO  
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files ;  
GO