Transaction log full with simple recovery mode

Category: sql server dbengine

Question

AragornEllessar on Fri, 08 Feb 2013 16:27:21


Hi,
we have a little db with recovery mode=simple.
So, with this mode, transaction log suppose to be truncate after each checkpoint.

I have a msg in the error log saying this: "The transaction log for database 'X' is full. Error 9002, Severity 17 state 4.

So, I run "select name, log_reuse_wait_desc from sys.databases" and the result show me than my "X database" have log_reuse_wait_desc = 'active_transaction'.

After I run "dbbc opentran" to see the active transaction, the result is "no active transaction".

So, I don't understand. log_reuse_wait_desc= 'active_transaction' and dbcc opentran= "no active transaction" ?

Do you have an idea ?

thanks and regards,

Replies

Praneeth Kumar Charla Gorla on Fri, 08 Feb 2013 16:29:59


is this database involved in replication?

Shiju Samuel on Fri, 08 Feb 2013 16:35:08


Did you ristrict your log growth and does the drive has enough space.

Thanks,

Shiju

Saurabh Sinha DBA on Fri, 08 Feb 2013 16:43:59


Hi buddy

Your value of log_reuse_wait_desc says all story the only problem is some active transaction

Run below query to find what is eating most of your log space. font forget to change database name in query

SELECT tst.[session_id], s.[login_name] AS [Login Name], DB_NAME (tdt.database_id) AS [Database], tdt.[database_transaction_begin_time] AS [Begin Time], tdt.[database_transaction_log_record_count] AS [Log Records], tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used], tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd], SUBSTRING(st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text, st.[text] AS [Last T-SQL Text], qp.[query_plan] AS [Last Plan] FROM sys.dm_tran_database_transactions tdt JOIN sys.dm_tran_session_transactions tst ON tst.[transaction_id] = tdt.[transaction_id] JOIN sys.[dm_exec_sessions] s ON s.[session_id] = tst.[session_id] JOIN sys.dm_exec_connections c ON c.[session_id] = tst.[session_id] LEFT OUTER JOIN sys.dm_exec_requests r ON r.[session_id] = tst.[session_id] CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp where DB_NAME (tdt.database_id) = <'database name>'

ORDER BY [Log Bytes Used] DESC;


Thanks

Saurabh Sinha



Tom Phillips on Fri, 08 Feb 2013 16:51:09


Every command in SQL Server is logged.  The recovery model controls when the log entries can be reused.

This would indicate someone ran a transaction which is larger than the current log can contain.

V. Keerthi Deep on Fri, 08 Feb 2013 16:54:46


Make sure to run dbcc opentran() on the database for which the log file is growing then you can find the open transaction information

AragornEllessar on Mon, 11 Feb 2013 13:18:50


Hi,

no we do not use replication

AragornEllessar on Mon, 11 Feb 2013 13:19:43


Yes I set maximal log space.

AragornEllessar on Mon, 11 Feb 2013 13:24:18


"Hi,

thanks for your answer.

I run your script and now I saw sql using log space. But all the sql in the result showed me log_records=0, log_bytes_used=0 and log_bytes_rsvd=0.

And I still have no transaction when I run "dbcc open tran" and "select * from sys.dm_exec_sessions".

So I still don't understand why I have "The transaction log for database 'X' is full. Error 9002, Severity 17 state 4." message in the error log  when during this time, no active transaction are using transation log ?

regards,

Saurabh Sinha DBA on Mon, 11 Feb 2013 13:53:22


Hi Aragon

Try running this and find wats in your log file actually

SELECT *
FROM ::fn_dblog(NULL, NULL)

/* Or query default trace to find who ran what and when. */

SELECT DB_NAME (DatabaseID) ,* FROM 
fn_trace_gettable( convert (varchar(1000),
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc')  , default) order by starttime desc

Thanks

Saurabh Sinha

AragornEllessar on Mon, 11 Feb 2013 16:16:17


Hi Saurabh Sinha,

thanks for your scripts.

I found my problem.

regards

Saurabh Sinha DBA on Mon, 11 Feb 2013 18:51:29


Hi Aragon

We will appreciate if you can share your findings and how exactly you confirmed the root cause.

Thanks

Saurabh Sinha