Trying to identify what process is making a connection to a specific DB in SQL Server

Question

I am attempting to track down... something... that is making a connection to a specific database without (apparently) doing anything with the connection. However, so far I have had no luck.

The data from this database has been migrated to other databases on the same server, and after the developers confirming that they had changed / removed all access to this particular database (including connection strings that were embedded but not used), we renamed the database to something like DBNAME_Old. However, this caused scattered errors to be generated. We renamed the DB back to the original name, and the errors disappeared. However, after examining their code, the devs still could not identify any other piece of code that could be causing the issues.

The code accessing the database is not accessible to me (or I would have searched it myself).

Next, I set up a scheduled job that attempted to identify any connections that were made to the database. I could identify processes running in the database via multiple methods, but nothing showed up while my monitoring was running (relatively infrequently, as no slamming production).

I then tried a couple of extended events procedures:

-- Attempt 1 - 
CREATE EVENT SESSION [DBToMonitorMonitoring] ON SERVER 
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
 ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
 WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DBToMonitor') AND [sqlserver].[session_server_principal_name]<>N'[THEDOMAIN\Domain_Login]')),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
 ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
 WHERE ([database_name]=N'DBToMonitor' OR [sqlserver].[database_name]=N'DBToMonitor')),
ADD EVENT sqlserver.logout(
 ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
 WHERE ([sqlserver].[database_name]=N'DBToMonitor')),
ADD EVENT sqlserver.rpc_starting(SET collect_statement=(1)
 ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
 WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DBToMonitor') AND [sqlserver].[session_server_principal_name]<>N'[THEDOMAIN\Domain_Login]')),
ADD EVENT sqlserver.sql_batch_starting(
 ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username)
 WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DBToMonitor') AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[session_server_principal_name],N'AMERICAS\DTNA_S_SQLService')))
ADD TARGET package0.ring_buffer(SET max_events_limit=(400))
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=ON,STARTUP_STATE=OFF)
GO
-- Attempt 2 - Found on the internet
CREATE EVENT SESSION [DBToMonitor_DB_Usage] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
 ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.username)
 WHERE ([sqlserver].[database_name]=N'DBToMonitor'))
ADD TARGET package0.event_file(SET filename=N'D:\Traces\DBToMonitor_DB_Usage.xel',metadatafile=N'D:\Traces\DBToMonitorDB_Usage.xem')
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

Although the second could identify direct connections made to the database, it could not identify cases such as SELECT TOP 1 * FROM DBToMonitor.dbo.SampleTable

Any recommendations for identifying any connection made to the data in a specific database, or any login even if no data is touched in the database, would be fully appreciated here, because I've reached the end of my resources here, other than to slam the server with continuous monitoring that would bring production to its knees.

Help?

Answer 1

One way is to set the database to AUTO_CLOSE and capture the database_started event as in the below example. Although I've included a database_id filter in this example, it's probably not needed since this will (hopefully) be the only AUTO_CLOSE database on the instance.

The database_name in the trace will reflect the context database where the database is used, such as in a cross-database query.

ALTER DATABASE DeprecatedDatabase
 SET AUTO_CLOSE ON;
GO
--get database_id value for the database
SELECT DB_ID(N'DeprecatedDatabase') AS DatabaseID;
GO
--specify above database_id value as session filter
CREATE EVENT SESSION DeprecatedDatabaseUsed ON SERVER 
ADD EVENT sqlserver.database_started(
 ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.server_principal_name)
 WHERE ([database_id]=(40)))
ADD TARGET package0.event_file(SET filename=N'DeprecatedDatabaseUsed',max_file_size=(100))
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
ALTER EVENT SESSION DeprecatedDatabaseUsed ON SERVER
 STATE = START;
GO

Note AUTO_CLOSE is generally a bad idea but I'd expect the database to be used infrequently in this case since it shouldn't be used at all.

I'll add that the app team should be able to identify where "scattered errors" occur in the code after the database is renamed. That would pinpoint where the database is being used.

Answer 2

What about a Logon Trigger?

You could insert the data for the application logging into the server into a table, then query it later to track the faulting application and server down.

• Laughing Vergil: This is actually checked for in the first Extended Events process: ADD EVENT sqlserver.login(SET collect_options_text=(1) ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.username) WHERE ([database_name]=N'DBToMonitor' OR [sqlserver].[database_name]=N'DBToMonitor')),
• Laughing Vergil: Apparently, if your login has permissions to access both database A and database B, running SELECT * FROM B.dbo.Table when you are in A does not trigger a login.
• Sean Gallardy: @LaughingVergil Logins happen at the server level, not the individual database level.
• Laughing Vergil: That is true. However, a logon trigger is not likely to identify my access to a database in the above cited case.