Tracking Connections To a Database

  • RonMexico

    Hall of Fame

    Points: 3340

    I'm trying to come up with a way to track the history of connections made to one database without setting up a trace or having a job run every minute. So far I've been focusing on the sys.dm_exec_sessions and sys.dm_exec_connections DMVs and each offers some of what I'm looking for (ID, host name, time) but neither seem to keep a complete history. I'm willing to even have history only go back as far as the last restart of the service. I'm hoping I can show a cumulative log so if user A runs a query at 8:30 and again at 9:00 then both of those will be captured along with other users. Any additional information such as what they were running, how long it took, etc are nice but not necessary since I'm mainly concerned with tracking the number of connections. Does anyone have any pointers to set me in the right direction?

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    Have you tried a SQL Audit?  You can control what you capture with very fine granularity.

    John

  • alevyinroc

    Grasshopper

    Points: 18

    Both SQL Audit and Extended Events (which Audit builds on) will do this for you. Here's an XE session I created to capture all queries executed against a specific database a few weeks ago:

    CREATE EVENT SESSION [Find YOURDB queries] ON SERVER 
    ADD EVENT sqlserver.rpc_starting(
    ACTION(package0.event_sequence,sqlserver.session_id)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'YOURDB')),
    ADD EVENT sqlserver.sql_batch_starting(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.username)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'YOURDB'))
    ADD TARGET package0.event_file(SET filename=N'C:\IT\XEData\FindYOURDBQueries.xel')
    WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
    GO


    There's a visual editor for XE sessions as well which can help you fine-tune the event filtering so that you can capture only connections.

    Question: are you counting only connections to the database, or any connection which accesses the database? IOW, would a connection that's using master but then querying your database via three-part naming ( select * from YOURDB.dbo.tablename ) need to be counted as well?

  • RonMexico

    Hall of Fame

    Points: 3340

    I've worked with Audits in the past to capture different events so I'll start there.

    My goal is to collect data to generate reports any time someone or something hits a database. Then I can run some analysis to say we had x number of things hit the database between this time and of those hits x were from distinct users.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    Use Audit or XE. XE is more friendly for data storage and loading into a table.

  • RonMexico

    Hall of Fame

    Points: 3340

    I've changed my focus to looking at XE. I was hoping capturing sql_batch_completed events would show how often the database is accessed. I set up my session to filter on the database name that I'm concerned with and what I'm finding is that if the connection is another database (master) and the query uses DatabaseName.SchemaName.TableName then it gets excluded. Is there an easier way to see what hits a database despite where it comes from?

    CREATE EVENT SESSION [DatabaseQueries] ON SERVER 
    ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.database_name)
    WHERE ([sqlserver].[database_name]=N'DatabaseName' AND [sqlserver].[sql_text]<>N'SELECT @@SPID;'))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO
  • John Mitchell-245523

    SSC Guru

    Points: 148761

    I suppose you could try filtering like this

    database_name = 'DatabaseName' OR sql_text LIKE '%DatabaseName.%' OR sql_text LIKE '%[[]DatabaseName].%'

    .Forgive me if I haven't escaped those square brackets correctly!

    John

  • RonMexico

    Hall of Fame

    Points: 3340

    That did the trick. I'll continue to monitor it to see if it gathers the data I'm looking for.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply