Tracking Connections To a Database

Question

Post reply

Tracking Connections To a Database

RonMexico

Hall of Fame

Points: 3474
More actions
April 16, 2020 at 3:05 pm

#3743472

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?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

John

alevyinroc SSC Rookie Points: 30 More actions · Answer 2

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: 3474 More actions · Answer 3

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: 736212 More actions · Answer 4

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

RonMexico Hall of Fame Points: 3474 More actions · Answer 5

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: 148809 More actions · Answer 6

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: 3474 More actions · Answer 7

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