Dear all,

Which is the faster and reliable way to obtain how many transactions has
been commited in a day in all the Sql Server db? Is there any system stored
procedure which get that information?

Thanks for your advices/comments/thoughts,

Regards,

Enric

I don't believe you can track committed transactions.

There's no perfmon counter or SQL profiler event I can think of that
would do the trick.  There's also no proc I know of to do so.  As far as
I am aware, the only transactions that are of any interest, in terms of
tracking, are open (or active) transactions, which you can see with
"SELECT @@TRANCOUNT", but that won't tell you how many transactions have
been committed over a certain period of time, just how many are
currently open.

Show quoteHide quote

@@total_read and @@total_write since last start also will help you

Show quoteHide quote

No they won't.  Those global vars have nothing to do with transactions.

@@TOTAL_READ
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_484k.asp)

@@TOTAL_WRITE
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_26p1.asp)

Show quoteHide quote

Thanks so much for that,

Show quoteHide quote

In any case, it would be gorgeous have available any job or sp be able to
show info such as this:

user1  select   12    2     10
user1  delete    3       1       2
user2  update   1

I've found this:

DBCC INPUTBUFFER (spid)

Language Event    0    select *  from VconexionesNAO where referencia='ACTUA' and
optapl='0'

Doing a loop with that info and then sorting it.


Returning the sentence



Show quoteHide quote

Partial solution
you can see BOL for global vaiables like
@@connections,@@transcount,@2packets_sent but all these give you no of
transactions since last start. you may use average of it per day.

r.d
Show quoteHide quote

@@connections has got nothing to do with transactions, it's the number
of connections (successful logins) since the last restart.

@@transcount is only the number of currently open transactions.

@@packets_sent has got nothing to do with transactions, it's the number
of packets sent out the server's NIC since the last restart.

There are some very informative global vars you can access (along with
the wealth of information available through perfmon counters & SQL
profiler traces) but none of that will tell you the number of
transactions (committed or otherwise) for a given time period.

The only place I can think of that this info is recorded is in the
transaction log.  If you were to read the transaction log with
probably tell.  Something like this:

    -- Number of BEGIN TRAN & COMMIT TRAN since last log truncation
    select Operation, count(*) from ::fn_dblog(null,null)
    where Operation in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')
    group by Operation

    -- Number of BEGIN TRAN & COMMIT TRAN today (assuming the log hasn't
    been truncated today)
    select Operation, count(*) from ::fn_dblog(null,null)
    where (Operation = 'LOP_BEGIN_XACT'  and convert(datetime,[Begin
    Time]) > convert(datetime,convert(varchar(8),getdate(),112)))
    or    (Operation = 'LOP_COMMIT_XACT' and convert(datetime,[End
    Time])   > convert(datetime,convert(varchar(8),getdate(),112)))
    group by Operation

Show quoteHide quote

sp_monitor reveals several server metrics, but not total transactions.
Also, if you can identify a suitable SQL Profiler event (perhaps
RPC:Completed,SQL:BatchCompleted), then you can have the event log output to
a table for periodic querying.
http://vyaskn.tripod.com/analyzing_profiler_output.htm
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=41&rl=1
http://www.microsoft.com/technet/prodtechnol/sql/70/tips/sqlprof.mspx

Show quoteHide quote

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)