1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

Transaction Log Full - SNAP_NO_NEW_ENTRY

Tuesday, 31 July 2012 11:16

Written by Prakash Palani

Print E-mail

Problem Statement : In one of the SAP BW production system, we noticed that 45.6% of transaction log was used, when we reported it to the functional team, they came back saying there are no dataloads/heavy transactions running in the system. This reaction from functional team triggered a question that is "Why in the world, the transaction log space does not get freed up even after performing the transaction log backup at regular intervals (15 minutes).

Reason : In this particular case, it was found that one of the active transaction was left like an orphan at the database level, which was blocking all the subsequent transactions to stay in the transaction log itself.

 

How do I proceed in such situations?

 

Step 1 : Find the reason for not getting the transaction log space back even  after successful regular transaction log backups

 

Execute the query using SQL Studio : select name, log_reuse_wait from sys.databases where name=

 

 

We can decide upon the next steps based on the value returned in column log_reuse_wait, in our case, it returned 4 which means there are active transactions in the transaction log. Subsequent steps are not valid if it returns any other value, for example, if it returns code 3, then please check the backup status using query [SELECT A.NAME, start_time as STARTTIME, B.TOTAL_ELAPSED_TIME/60000 AS [Running Time], B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining], B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM master..sysdatabases A, sys.dm_exec_requests B WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%' order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc ].

 

 

Code

Reason

0

Nothing

1

Checkpoint

2

Log backup

3

Active backup or restore

4

Active transaction

5

Database mirroring

6

Replication

7

Database snapshot creation

8

Log Scan

9

Other (transient)

 

Step 2: Now we know that there are active transactions in the system, the next step is to find which is the oldest open transaction in the system, please execute the following command to find it out.

 

dbcc opentran(‘')

 

This query returned a pid 192 and from the screen below, it is understood that this particular process is active at the database level since 9-01-2012 01:00:00 AM

 

 

 

Step 3 : Find out what exactly being performed by PID 192.

With the help of below query, we can find out the action that this being performed by the oldest PID which will give us a fair idea on how to deal with this particular process (i.e. Kill, etc.,)

SELECT s_tst.[session_id],
s_es.[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
s_tdt.[database_transaction_begin_time] AS [Begin Time],
s_tdt.[database_transaction_log_record_count] AS [Log Records],
s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
s_tdt.[database_transaction_log_bytes_reserved] AS [Log Rsvd],
s_est.[text] AS [Last T-SQL Text],
s_eqp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions s_tdt
JOIN sys.dm_tran_session_transactions s_tst
ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s_es
ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec
ON s_ec.[session_id] = s_tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests s_er
ON s_er.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;
GO

 

 

Step 4: Find out whether this particular process is really needed to be processed, if not to be processed and pretty old, seek approval from customer to kill this particular workprocess.

 

Looking at the above table, it is clear that session id 192 is related to which is basically for Java Stack and as mentioned below, it was trying to insert some content into table WCR_USERSTAT.

 

Background : Why this particular simple query on a very thin table is causing the huge amount of data to stay in transaction log file itself?

 

Look at the below table, at the time of sequence 107, INSERT WCR_USERSTAT is the only active transaction. Therefore, the active part of the transaction log stretches from 101, the start of the oldest transaction 101 that is still open, up to 107, the last log entry that is written. The inactive part in this example is represented by all of the entries that are before entry 100 and entry 100.

 

 

Due to Uncommitted transactions: If a user starts an explicit transaction (such as using a BEGIN TRAN statement) and then does a modification of some sort (such as a DDL statement or an insert/update/delete action), the transaction log records that are generated need to be kept around until the user either commits or rolls back the transaction. This means that any subsequent transaction log records generated by other transactions also cannot be freed, as the transaction log cannot be selectively freed. If that user, for example, goes home for the day without completing the transaction, the transaction log will continue to grow as more and more transaction log records are generated but can’t be freed. We can make use of the command, DBCC  OPENTRAN (‘Dbname’)  to identify if there are any open transactions in the database.

 

 

Consider the below example,
|—————————–|——-|—|
A                                              B           C    D


Where point A corresponds to the start of the log file. Let’s assume the point between A and B where some transaction has happened and point B represents the end of the transaction. The space from B to C represents an open transaction still running in SQL at the time of the log backup.  When you backup the log, the space from A – B is truncated and freed up for reuse, but because the B – C portion is still active, it cannot be released back for reuse.  When you shrink the file, it can only shrink the C-D Portion because the active portion of the log is still B-C.

Read more: http://sql-articles.com/articles/troubleshooting/log-file-growth-in-sql-server/#ixzz1izVbubHw

Conclusion : During such type of incidents, we should either restart SQL Inastance (or) Kill the hanging process at the database level (which should only be done by experienced DBAs)

 

{fcomment} {flike}

Share

 

Transaction Log Full - SNAP_NO_NEW_ENTRY