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,454
  • 13
  • 39
  • 61

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,681
  • 4
  • 27
  • 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
  • 19,148
  • 4
  • 37
  • 58