Step-by-step: Shrink Transaction Log File

 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).

1. Check the database recovery model

SELECT name, recovery_model_desc 

FROM sys.databases 

WHERE name = 'YourDatabaseName';

If the recovery model is FULL or BULK_LOGGED, shrinking the log file requires backing up the transaction log first.

2. (Optional) Backup Transaction Log (Only if Recovery Model is FULL or BULK_LOGGED)

BACKUP LOG YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_Log.trn';

3. Find the logical name of the log file

USE YourDatabaseName;

GO

EXEC sp_helpfile;

Note the logical name of the log file (usually ends with _log).

4. Shrink the log file

USE YourDatabaseName;

GO

DBCC SHRINKFILE (YourLogFileLogicalName, 1);  -- Shrink to 1 MB

You can change 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;