Shrinking the Large Log File in SQL Server

353 views 05:32 0 Comments 10 October 2019

Scenario

We recently came across a situation where one of the drives in the production server is almost full. There are two large databases and they were not backed up. No backup job or no maintenance plans were in place. That drive is filled by the log files of these two databases (~200GB each). This has happened because the databases were in full recovery mode but transaction logs are not backed up. As a result, log file grew and grew till it occupied maximum space available in the drive.

When we spotted this situation, we changed the recovery mode over to Simple hoping that would reduce the log file size.  However, the log file was still present, was still growing. Even though we are in Simple Recovery mode.

Solution

Here are the steps to solve this issue:

  1. Steps 1: Full backup
  2. Steps 2: Set the recovery model to simple
  3. Steps 3: Check any open transaction is going ON or NOT
  4. Steps 4: Check log backup is required or not before log file shrink
  5. Steps 5: Shrink the log file
  6. Steps 6: Check log file space
  7. Steps 7: Set the recovery model back to full
  8. Steps 8: Setup a job to backup transaction log frequently

Note: Shrinking the log file is not a good option to do it regularly. I had to use the shrinking option because this is one-off and the log file has grown that big is due to lack of log backups.

Steps 1: Full backup

We can take full backup either by using TSQL script or by SSMS.

By TSQL

BACKUP DATABASE [Our_Tech_Ideas] TO  DISK = N'F:\SQL DATABASE\Gama\Our_Tech_Ideas_FullBackup.bak' 
WITH NAME = N'Our_Tech_Ideas-Full Database Backup', COMPRESSION,  STATS = 10
GO

Result

By SSMS

Right-click on the database > Task > Back Up…

General > Destination > Back up to: > Disk > (Remove the path if any) > Add

Destination on disk > File name > Click on 3 dots (…) then select the destination folder & provide a name with ‘.bak’ extention. [Example: F:\SQL DATABASE\Gama\Our_Tech_Ideas_FullBackup.bak]. Then click ‘OK’

Media Option > As per requirement choose ‘Oerwrite media’ & Reliabiliy then Click ‘Backup Option’

Backup Option > Compression > Set backup compression as ‘Compress backup’ then click ‘OK’

Backup started, check the status on bottom left corner.

Database backup is successfully done. Click ‘OK’

Steps 2: Set the recovery model to simple

By TSQL

USE [master]
GO
ALTER DATABASE [Our_Tech_Ideas] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Result

By SSMS

Right click on database > Properties

Option > Recovery model

Recovery model > Full to Simple

Click ‘OK’

Steps 3: Check any open transaction is going ON or NOT

By TSQL script

Use [Our_Tech_Ideas] -- database_name
dbcc opentran
go

Result

Or to check open transaction we may use below script also

SELECT 
     tdt.transaction_id
     ,tst.session_id
     ,tdt.database_transaction_begin_time
     ,CASE tdt.database_transaction_type
        WHEN 1 THEN 'Read/write transaction'
	WHEN 2 THEN 'Read only transaction'
	WHEN 3 THEN 'System transaction'
      END transaction_type
     ,CASE tdt.database_transaction_state
	WHEN 1 THEN 'Transaction not initialized'
	WHEN 3 THEN 'Transaction has not generated by any log'
	WHEN 4 THEN 'Transaction has generated by log'
	WHEN 5 THEN 'Transaction Prepared'
	WHEN 10 THEN 'Transaction Committed'
	WHEN 11 THEN 'Transaction Rolled back'
	WHEN 12 THEN 'Transaction committed and log generated'
     END transaction_state     
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
  ON tst.transaction_id = tdt.transaction_id

The result will be shown in the below format. As we don’t have any open transaction so its showing blank.

Steps 4: Check log backup is required or not before log file shrink

By TSQL script

Use master
GO
select name, log_reuse_wait_desc from sys.databases
GO

Result

If ‘NOTHING’ then shrink activity can be done
If ‘LOG_BACKUP‘ then log backup must be taken before shrink activity
If ‘OLDEST_LOG‘ then full backup must be performed, then log back can be take before shrink activity

Steps 5: Shrink the log file

By TSQL

First, check the name of the log file

use [Our_Tech_Ideas] -- Database name
go
sp_helpfile
go

Result

In our case ‘name’ is AdventureWork2016_Log

TSQL script to Shrink the log file

USE [Our_Tech_Ideas] -- Database name
GO
DBCC SHRINKFILE (N'AdventureWorks2016_Log' , 18) -- File name & size
GO

By SSMS

Right-click on database need to shrink > Task > Shrink > Files

General > File type

File type > Log

Shrink action > Select 2nd option & provide the value as per requirement. Click ‘OK’

Steps 6: Check log file space

By TSQL

USE Our_Tech_Ideas
GO
DBCC SQLPERF(LOGSPACE);
GO

Result

Steps 7: Set the recovery model back to full

By TSQL

alter database Our_Tech_Ideas
set recovery full;
go

Steps 8: Setup a job to backup transaction log frequently

The final step to create a job or maintenance plan to take transaction log backup. We will show step by step process in our next post. Click Here

Leave a Reply

Your email address will not be published. Required fields are marked *