After session kill, T-log's size are increasing.

Question

I have huge DB and Table.
T-log used percent was high because of delete table.. so I killed the session. But killed session is remained 'killed/rollback' status.

I checked below query for check log use cause..

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) = 'USERDB'
ORDER BY [Log Bytes Used] DESC;

Result is interesting.. log bytes used going to high.. and Log bytes rsvd going to low..
What is mean ? Is it normal at rollback status ?
Why T-log percent is increasing ? Help me...

Log records Logbytesused Log bytes rsvd
22:20 6889505 3101314120 5247688844

10:00 9380608 3445806076 4894905284
10:01 9381974 3445971364 4894732570
10:02 9382674 3446068132 4894632362
10;09 9387157 3446632484 4894044120
13:22 9512556 3462295596 4877708434
14:05 9528071 3464266976 4875652972
14:48 9542724 3466521536 4873321072
15:26 9556239 3468447628 4871322958

Answer 1

Hi okjinlee-5533,

--Is it normal at rollback status?
Yes, it is normal. Once kill command happens, SQL Server must undo every step of the transaction that has been written to the transaction. And some large operations may take a long time to rollback.
Please refer to Rollback: What happens when you KILL a session which might help.

--Why T-log percent is increasing?
Transaction log will log rollback operation. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. This reserved space is freed when the transaction is completed.
Please refer to Transaction Log Logical Architecture for more details.

Best Regards,
Amelia

=======================================

Please remember to click " Accept Answer" and upvote the responses that resolved your issue.