Sunday, February 19, 2012

Connection / Transaction Statistics

I'm trying to capture the number of connections that are made to each
database on our server (whether by middleware clients, QA, etc).
Preferrably, I'd like to be able to capture this information on a daily
basis. Is this possible using SQL Server 2000? I've researched this to no
end and found information on the sysprocesses table, but this returns current
connection information with no historical data. Thanks for any ideas.
You could look at using profiler to cature this information
See Monitoring with SQL Profiler under Administering SQL Server in BOL
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> I'm trying to capture the number of connections that are made to each
> database on our server (whether by middleware clients, QA, etc).
> Preferrably, I'd like to be able to capture this information on a daily
> basis. Is this possible using SQL Server 2000? I've researched this to
no
> end and found information on the sysprocesses table, but this returns
current
> connection information with no historical data. Thanks for any ideas.
|||I've done this and am unable to get the server instance or the database name
to log. The database is more important. The trace shows the login name and
application for table scan events. I have the database name selected but it
isn't populating. Am I missing something simple?
"Simon Worth" wrote:

> You could look at using profiler to cature this information
> See Monitoring with SQL Profiler under Administering SQL Server in BOL
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> no
> current
>
>
|||You're right, the database name won't come through, but ussually database id
does. You can find out what the name of the database is by using the id on
sysdatabases in master like this
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
WHERE dbid = <<The id from profiler>>
ORDER BY dbid
GO
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> I've done this and am unable to get the server instance or the database
name
> to log. The database is more important. The trace shows the login name
and
> application for table scan events. I have the database name selected but
it[vbcol=seagreen]
> isn't populating. Am I missing something simple?
> "Simon Worth" wrote:
daily[vbcol=seagreen]
to[vbcol=seagreen]
|||Easier still is just use DB_NAME, select DB_NAME (yournumber)
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:%23s$pCCOIFHA.1996@.TK2MSFTNGP12.phx.gbl...
> You're right, the database name won't come through, but ussually database
id
> does. You can find out what the name of the database is by using the id
on[vbcol=seagreen]
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
but[vbcol=seagreen]
> it
message[vbcol=seagreen]
each[vbcol=seagreen]
> daily
this[vbcol=seagreen]
> to
returns[vbcol=seagreen]
ideas.
>
|||Very cools tips, thank you. My last question is how I can have this run
without having to have a Trace Window open. Is there another tool/process
that can compile this same information and write it to a table?
"Simon Worth" wrote:

> You're right, the database name won't come through, but ussually database id
> does. You can find out what the name of the database is by using the id on
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
> it
> daily
> to
>
>

No comments:

Post a Comment