0

The transaction log file size is nearing 100% on one database. I run the below and both return 0 so no active transaction running.

The last query tells me that the database is in SIMPLE mode, but 'ACTIVE_TRANSACTION' is returned in log_reuse_wait_desc column.

However, I cannot see any running transaction! I have tried querying sys.dm_tran_database_transactions but do not see any transaction for this specific database.

How do I find this transaction? 

SELECT @@TRANCOUNT;

SELECT XACT_STATE();

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;
K09
  • 1,394
  • 13
  • 36
  • 59

2 Answers2

0

You can use the following DMV to view any transactions that might be causing your log file to be bloated: sys.dm_tran_database_transactions.

Both your queries are limited to your current connection, meaning they give you no information about other connections made to the SQL server.

@@Trancount; XACT_STATE

Reaces
  • 2,601
  • 4
  • 25
  • 38
0

Well if there is active transaction you have to wait for it to finish. You cannot do much. If you have multiple disks in your environment add a log file on other drive and allo wtransaction log to grow and let active transaction complete.

Add log file to database

After the transaction finishes you can try shrinking the log file. Please dont make shrinking a habit it do causes performance issue. After shrinking you can remove the log file you added

Remove log file from database

In some cases you can kill the transaction as well but I would not suggest that. I have seen in some cases where active transaction was stuck and killing it resolved the issue.

Edit: I guess if you have searched more on net you could have found more relevant articles related to what do to when log file is almost full. Develop a habit to help yourslef it will benefit you

Shanky
  • 18,985
  • 4
  • 35
  • 58
  • 5
    if you're going to recommend adding a second log file, at least add the pertinent advice to remove it once you're done. – Aaron Bertrand Aug 29 '14 at 12:55
  • Yes correct edited the answer – Shanky Aug 29 '14 at 12:59
  • Can somebody who downvoted give reason why you did on Question which is closed and Old. Other misuse of downvote – Shanky May 18 '15 at 13:41
  • wasn't me but people can down-vote for whatever reason they like. Age or state of the question doesn't prevent someone from finding and reading your answer, or reacting to it. Badgering people to leave a reason isn't going to have the effect you want. – Aaron Bertrand May 18 '15 at 17:59
  • I was more interested in knowing the reason so that I can improve the answer. Atleast this is why downvote is there as a medium of feedback. Personally I believe a downvote should have reason and chance be given to improve the answer. Plus my answer doesn't looks like it needs a downvote – Shanky May 18 '15 at 18:15
  • 1
    Please review this conversation. You're just never going to get the feedback of why some person didn't like your answer, sorry. There was a time I agreed with you but now I agree with most that it is just noise... – Aaron Bertrand May 18 '15 at 19:59
  • You'll also have to accept that sometimes there is a downvote without a good reason at all. – Aaron Bertrand May 18 '15 at 20:13
  • i want to down vote (but not gonna do that), and my reason is this: your provided solution is not an answer for this problem, I have same problem and I want to find the root cause of this, my next log file will get filled eventually, adding another log file may solve problem temporary but I'm here because I don't want follow this path – Amirreza Apr 03 '20 at 03:16