How to Shrink the Database Or Files In SQL Server

You can use the DBCC SHRINKDATABASE or DBCC SHRINKFILE command to shrink the database or specific data files. However, be cautious when using these commands, as shrinking can cause fragmentation and impact performance.

-- Shrink the entire database
DBCC SHRINKDATABASE('Your_Database_Name');

-- Shrink a specific data file
DBCC SHRINKFILE('Your_DataFile_Name', SIZE);

Replace Your_Database_Name with the name of your database, Your_DataFile_Name with the logical name of your data file, and SIZE with the target size in megabytes.

For example :

Shrink Database

Shrinking a database involves reducing the physical size of its data and log files. However, it’s essential to use the DBCC SHRINKDATABASE command with caution, as excessive shrinking can lead to performance issues and file fragmentation. It’s often better to manage the size through proper database maintenance practices like regular backups and appropriate sizing strategies. Here’s an example of how you can use DBCC SHRINKDATABASE:

-- Shrink the entire database
USE Your_Database_Name; -- Replace YourDatabaseName with the name of your database

-- Optional: Check the current size of the database files
DBCC SQLPERF(logspace);

-- Shrink the database
DBCC SHRINKDATABASE(Your_Database_Name);

-- Optional: Check the size of the database files after shrinking
DBCC SQLPERF(logspace);

Shrink Files (Data file or Log file)

Alternatively, if you want to shrink a specific data file rather than the entire database, you can use the DBCC SHRINKFILE command, please check your logical name of your files.

Use [Your_Database_Name]
Alter Database [Your_Database_Name] Set Recovery Simple
DBCC SHRINKFILE([Your_DataFile_Name_Log], 1)
DBCC SHRINKFILE([Your_DataFile_Name_Data], 1)
Alter Database [Your_Database_Name] Set Recovery Full

Again, use these commands judiciously, and always monitor the impact on your database’s performance. It’s generally recommended to size your database appropriately from the beginning and to rely on regular database maintenance practices to keep it in good health.