[dba-SQLServer] Monitoring concurrent users

Thanks Marty!

I'll give it try.

John B. 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
MartyConnelly
Sent: Wednesday, October 19, 2005 2:22 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Monitoring concurrent users

Maybe not what you want, here are some notes I made on checking MSDE for
throttling violations above 5, maybe a start.

You can track the amount of throttling going on through DBCC and logs I once
tracked this and kept a log in an access table.

There is more here or SQL BOL search on ( DBCC CONCURRENCYVIOLATION )

Watch out below for yellow on white tips (kind of hard to read in IE click
on area to see.

http://www.betav.com/Files/Content/Whitepapers/msde_files/msde.htm


To track the throttling you can use something like SP below with DBCC or
turn it on in the SQL log, it gives you the number of times you have exceed
the 5 connection limit. This dumps the records to database table.

Concurrency violations since 2003-01-21 12:20:43.747
1     2     3     4     5     6     7     8     9  10-100  >100
0     0     0     0     0     0     0     0     0       0     0
Concurrency violations will be written to the SQL Server error log.

ALTER PROCEDURE StoredProcedureDBCCworks
/*
(
 @parameter1 datatype = default value,
 @parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT ON
-- Create the table to accept the results or use #tracestatus #indicates
temptable If NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID =
OBJECT_ID('DBCCtracestatus ') AND sysstat & 0xf=3) DROP TABLE
DBCCtracestatus BEGIN CREATE TABLE DBCCtracestatus (
  maint varchar(4096),
  TraceFlag INT,
  Status INT
  )
END
I lifted parts of it I think from Betav.com( no longer there), but is pretty
basic calls.
The only thing unusual is the select statement.
I just put it together for a client group that was interested; can't find my
final code I would rewrite but need a network to test on ; or run a dozen or
so access mssql clients from my machine to check. You can do this by hand
starting the dbcctrace to sql error logging from OSQL then sifting through
the error log. Sorry about the late reply tried tracking down the code

John Bartow wrote:

>Hi all,
>I'm looking for suggestions on monitoring concurrent users for a MSDE 
>2000 based application. Over the next few months I want to see what the 
>average and maximum number of users concurrently logged into the database
are.
>
>John B.
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>  
>

--
Marty Connelly
Victoria, B.C.
Canada



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com