Log file FULL

 Log file FULL. Troubleshooting steps in SQL Server.

 1.    Check the log file size by using below command.

DBCC Sqlperf(Logspace)

 

Database Name	Log Size (MB)	Log Space Used (%)	Status
Master	3.742188	37.99582	0
Tempdb	7.992188	14.07625	0
Model	7.992188	14.95601	0
Msdb	1.242188	40.8805	0
Sqldbahub	135.9922	1.508014	0

If log space used % is more than 70%,  Check auto growth setting. If auto growth not set properly set it.

If auto growth setting is correct.  Then check disk space.

2.    Find the any open transactions by the below statement.

DBCC Opentran(sqldbahub)

If no open transactions the Shrink the Log file. If log file is unable to shrink then check next step.

Step 1 : Login into the SQL server management studio with “SA” user

Step 2: Expand the databases > and select the database name which is having transaction log size full issue

Step 3: Right Click on database name and go to Task > Shrink > Files

Step 4: Here, in this step, you have to select “File Type” as “Log” and “File Name” of the transaction log file. And then, you will be seeing a check box in name of release unused space. Click on the check box and this enables us to reduce the transaction log files space but it doesn’t delete any files to reduce the space.

Step 5: You can find another check box option called “reorganize files before unused space”. When you select this option it will allow you to enter the value for shrinking the logs. If you use this option then this is permanent so by default, your process will be cleared. by doing this you mean to reduce the rows and to relocate on unallocated pages.

Step 6: Here, you can choose the percentage left out for database shrinkage. The percentage count can be changed between 0 and 99. But this step is enabled only when you select 2nd option which is “reorganize files before releasing unused space”.

Step 7: The third option you can go with is an empty file by migrating the data to other files in the same file group. From the same group, it moves the files to a different location after then, the open space file can be deleted.

Increase the Transaction Log file size if enough disk space available:

Another way to deal with this issue is to increase the actual size of the Log file to do so please follow the below steps.

Step 1 : Login into the SQL server management studio with “SA” user

Step 2: Expand the databases > and select the database name which is transaction log size issue

Step 3: Right click on the database and click on “Properties”

Step 4: Go to the “Files” section from the left-hand menu list as shown below and update the LOG size on the right-Hand Side under the Size(MB) section as shown below.

3.    On log_reuse_wait_desc, column will give the exact information of logfile issue.

select @@servername as Server_name ,name,log_reuse_wait_desc from sys.databases

Server_name	name	log_reuse_wait_desc
SQLDBAHUB	master	NOTHING
SQLDBAHUB	tempdb	NOTHING
SQLDBAHUB	Model	NOTHING
SQLDBAHUB	Msdb	NOTHING
SQLDBAHUB	Sqldbahub	NOTHING

 It may be due to below specified issues. Below is the brief about log_reuse_wait_desc value:

Take action according the log_reuse_wait_desc value.

log_reuse _wait value	log_reuse_wait_desc value	Description
0	NOTHING	Currently there are one or more reusable virtual log files (VLFs).
1	CHECKPOINT	No checkpoint has occurred since the last log truncation, or the head of the log hasn't yet moved beyond a virtual log file (VLF). (All recovery models) This is a routine reason for delaying log truncation. For more information, see Database Checkpoints (SQL Server).
2	LOG_BACKUP	A log backup is required before the transaction log can be truncated. (Full or bulk-logged recovery models only) When the next log backup is completed, some log space might become reusable.
3	ACTIVE_BACKUP_OR_RESTORE	A data backup or a restore is in progress (all recovery models). If a data backup is preventing log truncation, canceling the backup operation might help the immediate problem.
4	ACTIVE_TRANSACTION	A transaction is active (all recovery models): A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. Long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint. A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions (SQL Server). Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.
5	DATABASE_MIRRORING	Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only) For more information, see Database Mirroring (SQL Server).
6	REPLICATION	During transactional replications, transactions relevant to the publications are still undelivered to the distribution database. (Full recovery model only) For information about transactional replication, see SQL Server Replication.
7	DATABASE_SNAPSHOT_CREATION	A database snapshot is being created. (All recovery models) This is a routine, and typically brief, cause of delayed log truncation.
8	LOG_SCAN	A log scan is occurring. (All recovery models) This is a routine, and typically brief, cause of delayed log truncation.
9	AVAILABILITY_REPLICA	A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model) For more information, see Overview of Always On Availability Groups (SQL Server).
10	-	For internal use only
11	-	For internal use only
12	-	For internal use only
13	OLDEST_PAGE	If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation. (All recovery models) For information about indirect checkpoints, see Database Checkpoints (SQL Server).
14	OTHER_TRANSIENT	This value is currently not used.
16	XTP_CHECKPOINT	An In-Memory OLTP checkpoint needs to be performed. For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables) For more information, see Checkpoint Operation for Memory-Optimized Tables and [Logging and Checkpoint process for In-Memory Optimized Tables] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

4.    Request Windows team to add more space on existing drive with capacity team approval.

5.    Add T-Log file in another drive and disable auto growth of first log file and enable newly added logfile if no space on existing drive. We can remove newly added log file once process completed when clear log file.

6.    Find the long running transactions and kill after approval.

-- check blocked by SPID details

    exec SP_Whoisactive 

    DBCC INPUTBUFFER(SPID)

    DBCC inputbuffer(58)

--If select command then kill the SPID ( Select command is not modify the data)

    KILL 58

 

7.      If log backup is required, then trigger Log backup and shrink logfile.

BACKUP LOG [sqldbahub] TO  DISK = N'C:\MSSQL16.MSSQLSERVER\MSSQL\Backup\sqldbahub.bak' WITH COMPRESSION,  STATS = 10

 

8.      Final option if recovery model is full change to simple then shrink the log file. Next simple change to full recovery model then shrink the log file.

--Check recovery model

SELECT recovery_model_desc FROM sys.databases WHERE name = 'sqldbahub'

 

--Change recovery model to simple

ALTER DATABASE sqldbahub SET recovery simple

 

--Check recovery model

SELECT recovery_model_desc FROM sys.databases WHERE name = 'sqldbahub' --After

 

-- Note the size of the log before shrink

EXEC sp_helpdb sqldbahub

 

-- shrink log to 1 GB chunk wise

DBCC shrinkfile(MyDB_log, 1024)

 

-- Note the size of the log after shrink

EXEC sp_helpdb MyDB

 

--Check disk space

9.      If issue is frequently occurring during maintenance jobs then add extra drive with proper size and configuration when no scope to expand existing drive. Detach from one drive and attach the log file to newly added drive.

10. Below script will help to generate log file shrink for all user databases.

DECLARE @ScriptToExecute VARCHAR(MAX);

SET @ScriptToExecute = '';

SELECT @ScriptToExecute = @ScriptToExecute +

'USE ['+ d.name +']; CHECKPOINT; DBCC SHRINKFILE ('+f.name+');'

FROM sys.master_files f

INNER JOIN sys.databases d ON d.database_id = f.database_id

WHERE f.type = 1 AND d.database_id > 4

-- AND d.name = 'NameofDB'

SELECT @ScriptToExecute ScriptToExecute

EXEC (@ScriptToExecute)

If you are getting Error 9002: The transaction log for database is full then follow above steps only for resolve the issue.