Number of transactions

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,
EnricI 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.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Enric wrote:

>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
>
>|||Partial solution
you can see BOL for global vaiables like
@.@.connections,@.@.transcount,@.2packets_sen
t but all these give you no of
transactions since last start. you may use average of it per day.
r.d
"Enric" wrote:

> 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 store
d
> procedure which get that information?
> Thanks for your advices/comments/thoughts,
> Regards,
> Enric
>|||@.@.total_read and @.@.total_write since last start also will help you
"Mike Hodgson" wrote:

> 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.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Enric wrote:
>
>|||@.@.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
::fn_dblogs or some 3rd party app (like Lumigent Log Explorer) you could
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),getd
ate(),112)))
or (Operation = 'LOP_COMMIT_XACT' and convert(datetime,[End
Time]) > convert(datetime,convert(varchar(8),getd
ate(),112)))
group by Operation
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:
>Partial solution
>you can see BOL for global vaiables like
> @.@.connections,@.@.transcount,@.2packets_sen
t but all these give you no of
>transactions since last start. you may use average of it per day.
>r.d
>"Enric" wrote:
>
>|||No they won't. Those global vars have nothing to do with transactions.
@.@.TOTAL_READ
(http://msdn.microsoft.com/library/d.../>
ls_484k.asp)
@.@.TOTAL_WRITE
(http://msdn.microsoft.com/library/d.../>
ls_26p1.asp)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:
>@.@.total_read and @.@.total_write since last start also will help you
>"Mike Hodgson" wrote:
>
>|||Thanks so much for that,
"Mike Hodgson" wrote:

> No they won't. Those global vars have nothing to do with transactions.
> @.@.TOTAL_READ
> (http://msdn.microsoft.com/library/d...
bals_484k.asp)
> @.@.TOTAL_WRITE
> (http://msdn.microsoft.com/library/d...
bals_26p1.asp)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> R.D wrote:
>
>|||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
"Mike Hodgson" wrote:

> No they won't. Those global vars have nothing to do with transactions.
> @.@.TOTAL_READ
> (http://msdn.microsoft.com/library/d...
bals_484k.asp)
> @.@.TOTAL_WRITE
> (http://msdn.microsoft.com/library/d...
bals_26p1.asp)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> R.D wrote:
>
>|||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/cont...&seqNum=41&rl=1
http://www.microsoft.com/technet/pr...ps/sqlprof.mspx
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:58B2337B-7A9E-4945-89BB-B95F1431584B@.microsoft.com...
> 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
>