To clear or shrink the log files in SQL Server and free up space, you can use DBCC SHRINKFILE along with a few preparatory steps, depending on your database recovery model.
⚠️ Important Notes Before Proceeding:
-
Shrinking log files should not be done frequently, as it can lead to fragmentation and performance issues.
-
Ensure this is done only when necessary (e.g., log file grew due to a large operation, and now it's not needed).
_log).1 to a more reasonable value (e.g., 1000 for 1 GB) depending on how much space you want to retain.🔁 To Apply on All Databases:
You can generate the script dynamically for all databases using the following:
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE' AND database_id > 4; -- Exclude system DBs
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
USE [' + @DBName + '];
DECLARE @LogFile NVARCHAR(128);
SELECT @LogFile = name FROM sys.database_files WHERE type_desc = ''LOG'';
DBCC SHRINKFILE (@LogFile, 1);
';
EXEC (@SQL);
FETCH NEXT FROM db_cursor INTO @DBName;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
✅ Optional: Change Recovery Model to SIMPLE Temporarily
Only do this if point-in-time recovery is not needed:
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (YourLogFileLogicalName, 1);
GO
ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
