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:
- Steps 1: Full backup
- Steps 2: Set the recovery model to simple
- Steps 3: Check any open transaction is going ON or NOT
- Steps 4: Check log backup is required or not before log file shrink
- Steps 5: Shrink the log file
- Steps 6: Check log file space
- Steps 7: Set the recovery model back to full
- 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
|
1 2 3 |
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
|
1 2 3 4 |
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
|
1 2 3 |
Use [Our_Tech_Ideas] -- database_name dbcc opentran go |
Result
Or to check open transaction we may use below script also
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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
|
1 2 3 4 |
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
|
1 2 3 4 |
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
|
1 2 3 4 |
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
|
1 2 3 4 |
USE Our_Tech_Ideas GO DBCC SQLPERF(LOGSPACE); GO |
Result
Steps 7: Set the recovery model back to full
By TSQL
|
1 2 3 |
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
