[dba-SQLServer] Monitoring concurrent users

Marty; that is simply brilliant.

Jim

-----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 12: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