Reducing/shrinking the SQL file size is essential for optimising database performance, saving storage space, and confirming efficient data management.
Follow the steps-
- Open SQL Server Management Studio and right-click on the database.
- Choose “Properties” > “Options”.
- Set “Recovery mode” to “Simple” and click OK.
- Right-click the database again, choose “Tasks” > “Shrink” > “Files”.
- Change the file type to “Log”.
- Set the log file size to 100 MB and click OK.
- Check the folder to confirm the size reduction.
- Alternatively, use these SQL queries-
ALTER DATABASE mydatabase SET RECOVERY SIMPLE;
DBCC SHRINKFILE (mydatabase_Log, 1);Warning – The first SQL script targets the main database, while the second script targets the log database.
After completing these steps, check if the folder size has been reduced.
Note – After changing the recovery model from Full to Simple and shrinking the transaction log, if you switch the database back to Full recovery mode take a new Full Database Backup immediately. This is required before transaction log backups can be resumed as changing the recovery model breaks the existing log backup chain.
This concludes our article. We hope you found it helpful. If you enjoyed the article, you will definitely appreciate our support services.