If you’re managing multiple databases like I do, you’ll know how important it is to automate repetitive tasks. One such task is reducing the size of SQL Server log files that have grown too large. Here’s a quick and effective way to shrink them using a T-SQL script.
This is one of the best suggestion in which is done using query. Good for those who has a lot of databases just like me. Can run it using a script.
USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (Document_Movement_upload_20171003_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GONote: You just pest your log here.
🔁 Why This Method Works
- Changing the recovery model to SIMPLE temporarily allows SQL Server to truncate inactive log records.
DBCC SHRINKFILEthen reclaims unused space.- Setting the recovery model back to FULL ensures you can resume regular transaction log backups.
🎯 When to Use This
- You’re running out of disk space due to large
.ldffiles. - You don’t need point-in-time recovery for a short maintenance window.
- You want to automate this process across multiple databases using scripts.
🔗 Reference
For more details, visit the original Stack Overflow post.
📌 Important Notes
- Always back up your database before changing recovery models.
- Don’t use this on production systems frequently; shrinking logs should not be a regular operation.
- Ensure proper transaction log backups are scheduled if you’re using the FULL recovery model.
Tag: #
For More Please Visit: https://stackoverflow.com/questions/829542/how-do-i-decrease-the-size-of-my-sql-server-log-file
✅ We hope these questions helped reinforce your database Service knowledge! Got more database questions or suggestions? Drop a comment below or check out more tutorials at Techtweet.xyz.
