June 18, 2015 at 1:57 pm
Consider this:
Your initial Transaction Log size is 1GB
You are rebuilding a Clustered Index (it is about 16GB in size) and
it is the only transaction in the database:
alter index [PK_Project_members] on jm_tl_t01.dbo.project_members rebuild
with (online=on,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99)
While the index is being rebuild you constantly poll "sys.dm_tran_session_transactions" and you see "database_transaction_log_bytes_used"=136 and "database_transaction_log_bytes_reserved"=9170.
These values never change till the index rebuild is done.
Note that this index rebuild causes the transaction log to grow over 40GB.
The question is why "sys.dm_tran_session_transactions" does not correctly report bytes used by index rebuild. Note that on the other hand the transaction log space used by "Create Index" operation is reported by "sys.dm_tran_session_transactions" correctly.
Any insight on this phenomena is appreciated.
June 18, 2015 at 2:36 pm
Well, none of that information about log usage is in sys.dm_tran_session_transactions; it's in sys.dm_tran_database_transactions.
Most likely you're doing an INNER JOIN between sys.dm_tran_session_transactions and sys.dm_tran_database_transactions on transaction_id while doing an online index rebuild.
That starts different transactions that generate most of the logging, but are not tied to a session in sys.dm_tran_session_transactions.
If you get rid of that join, run the online rebuild, and then check sys.dm_tran_database_transactions, you'll see some transactions with lots of log used, but they're separate transactions from the one tied to your session.
The logging will also be associated with the session's transaction if it is done offline, if I remember correctly.
You can also track those down by looking in sys.dm_tran_active_transactions, because they'll show up with a name of OnlineIndexInsertTxN there.
Cheers!
June 18, 2015 at 3:03 pm
You are correct. It was my typo. I join "sys.dm_tran_database_transactions" with "sys.dm_tran_session_transactions" and then with "sys.dm_exec_requests" and then with "sys.dm_exec_sql_text" to confirm that the transaction corresponds to sql statement I'm interested in. "database_transaction_log_bytes_used" and "database_transaction_log_bytes_reserved" columns I mentioned in the original post indeed come from "sys.dm_tran_database_transactions" . The rest of my statements remain the same and the question stands.
June 18, 2015 at 3:29 pm
Hmmm...I'm probably missing something, but didn't I explain that? 🙂
...while doing an online index rebuild. That starts different transactions that generate most of the logging, but are not tied to a session in sys.dm_tran_session_transactions.
If you get rid of that join, run the online rebuild, and then check sys.dm_tran_database_transactions, you'll see some transactions with lots of log used, but they're separate transactions from the one tied to your session.
The logging will also be associated with the session's transaction if it is done offline, if I remember correctly.
You can also track those down by looking in sys.dm_tran_active_transactions, because they'll show up with a name of OnlineIndexInsertTxN there.
If there's something unclear there, then by all means ask me to clarify!
Cheers!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply