CODEWITHSUNDEEP
c#sql-serverado.net
Kyeotic
Kyeotic

Reputation: 19867

Determine if SqlTransaction wrote data or was read only

I'm going to start off by saying I am pretty sure this is not possible. Googling has not turned up anyone asking this question, so I am pessimistically hopeful.

I am using a SqlTransaction to connect to a database, and allowing several consumers to use this transaction before I close it. Is it possible to determine whether or not the transaction was only used to read data or to read/write data, even when stored procedures may have been used? Does some property or SQL method (other than performing some unholy diff) exist that can check this?

Upvotes: 4

Views: 169

Answers (3)

usr
usr

Reputation: 171236

sys.dm_exec_sessions has a column writes. You can query it like this:

SELECT writes FROM sys.dm_exec_sessions WHERE session_id = @@SPID

That will impose some overhead, though. I think that DMV is O(N) in the number of sessions running. The system will slow down the more sessions there are. Eventually it becomes a case of negative scaling.

Note, that this is a per-session value. If you have multiple transactions per session (per opening of a SqlConnection object) the writes will be tracked cumulatively. You'd need to account for that, or use the idea in the other answer.

Upvotes: 3

Dave C
Dave C

Reputation: 7402

You can use the sys.dm_ views to check Transaction status.

When a transaction is opened, but no work is done (select only), it doesn't register, but if you do any work, the transaction log size is increased.

Try this first... note your SPID.

USE tempdb
begin tran
create table dave (id int)
insert into dave (id) select 1
-- rollback tran

Leave the transaction open, then look at it with this (replace your spid):

select database_transaction_log_bytes_used, *
from sys.dm_tran_database_transactions dt 
inner join sys.dm_tran_active_transactions at on (at.transaction_id = dt.transaction_id)   
inner join sys.dm_tran_session_transactions st on (st.transaction_id = dt.transaction_id) 
where (dt.database_id = DB_ID('tempdb')) and (st.is_user_transaction=1) 
and session_id=83

Then rollback the original.

Then try this:

USE tempdb
begin tran
select getdate()
-- rollback tran

And run the above on the sys.dm_ views...

The view is blank until there is something logged in the transaction. Selects do not get logged.

Don't forget to roll it all back.

So you could write a proc/view that you could query before closing your transaction, assuming you know the spid (easy enough to query for with SELECT @@SPID) that would return you the log size of the active session.

Upvotes: 2

Oscar Bralo
Oscar Bralo

Reputation: 1907

mmm, This is only an idea. I am thinking about something like.. creating a table in your database, calling CountRead, and another table CountWrite, and then, modify your querys to write in read table if the query is a SELECT and in the write table if the query is an INSERT or something. Then, when you want to check if only read or write, you only need to read this tables (if they already have data, you can or remove all the data when your application starts, or check the count in the beginning).

Then with a simple SELECT to this tables, you will be able to check if only read querys have been used, or only write querys, or both, and how many times.

As I said, is only an idea. ;) I hope this helps, or at least give you some idea about how to do it

Upvotes: 0

Related Questions