The transaction log for database & #39; xx & #39; is full, Error: 9002, Severity: 17, State: 2,9002 severity

The transaction log for database 'xx' is full, Error: 9002, Severity: 17, State: severity

Check database logs with the following error message:

Error: 9002, Severity: 17, State: 4.The transaction log for database 'SharedServices1_Search_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

View the usage of the current log:

The log here is not completely full, but it has occupied 79% of the 70 GB log file and more than 50 GB. I personally feel this is not normal. I have read the SQL Server 2012 implementation and management practices guide, which is described in the log section:

Due to application design problems, some connections may leave a transaction in SQL Server, rather than committing it in time. SQL Server will not interfere with this behavior. As long as the connection does not exit, the transaction will always exist until the client actively commits or rolls back. All the log records starting at the time when the transaction was started will be retained by the SQL Server (it is useless for log backup ).

Therefore, the existence of long transactions is the most suspicious clue.
In the system view sys. databases, view the log_reuse_wait and log_reuse_wait_desc values of the database 'sharedservices1 _ Search_DB:

The value of log_reuse_wait_desc is 'Active _ transaction'
That is to say, the log is waiting for the transaction checkpoint. It is further proved that large transaction logs are caused by long transactions.
For a description of the sys. databases view, see (https://msdn.microsoft.com/zh-cn/library/ms178534.aspx+%20+%22%E5% AE %98%E6%96%B9%E8%AF%B4%E6%98%8E%22)

View long transactions:

currentdate2015-04-08 14:47:42.430

We can see that a long transaction is running and has been running for nearly 90 minutes.
View transaction information:

select session_id,transaction_id,is_user_transaction,is_local from sys.dm_tran_session_transactions where is_user_transaction=1session_id  transaction_id  is_user_transaction is_local1566         3452140915             1              1

View the specific content of transaction according to session_id:

select s.text from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_Handle) s where session_id=1566textCREATE PROCEDURE dbo.proc_MSS_Crawl ..............................

You can also use transaction_id to check the current status of the transaction:

select transaction_begin_time, case transaction_type when 1 then 'Read/Write transaction' when 2 then 'Read-Only transaction' when 3 then 'System transaction' when 4 then 'Distributed transaction' end tran_Type, case transaction_state when 0 then 'not been comoletely initaialiaed yet' when 1 then 'initaialiaed but ha notstarted' when 2 then 'active' when 3 then 'ended (read-only transaction)' when 4 then 'commit initiated for distributed transaction' when 5 then 'transaction prepared and waiting resolution' when 6 then 'commited' when 7 then 'being rolled back' when 0 then 'been rolled back' end transaction_state from sys.dm_tran_active_transactions where transaction_ID=3452140915 transaction_begin_time  tran_Type   transaction_state2015-04-08 13:13:52.040 Read/Write transaction  active

After confirming the statement, you can find the developer to see why.