Capture all SQL Connection to table and schedule a job

Question

Post reply

Capture all SQL Connection to table and schedule a job

hello_san

Old Hand

Points: 328
More actions
November 29, 2018 at 12:13 pm

#376496

Does anyone have a script to Capture all SQL Connection to table and schedule a job that deletes the entry from the table after 7 days?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Joe Torre

SSChampion

Points: 10248

November 29, 2018 at 1:03 pm

#2014998

You can log logins

Sue_H SSC Guru Points: 90860 More actions · Answer 2

hello_san - Thursday, November 29, 2018 12:13 PM
Does anyone have a script to Capture all SQL Connection to table and schedule a job that deletes the entry from the table after 7 days?

One problem is that connections don't connect to a table. They connect to the server and may be in a particular database but nothing with tables.

Sue

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

hello_san - Thursday, November 29, 2018 12:13 PM
Does anyone have a script to Capture all SQL Connection to table and schedule a job that deletes the entry from the table after 7 days?

The question not being asked, and needs to be, what are you trying to accomplish?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

hello_san - Thursday, November 29, 2018 12:13 PM
Does anyone have a script to Capture all SQL Connection to table and schedule a job that deletes the entry from the table after 7 days?

Sounds like you should be using Extended Events to capture this kind of information. You can capture the code being executed (as was noted, there isn't a "connection to a table" to capture). I assume you're trying to track queries. This is one of the best ways to get it done. Another option since you're in 2017 is to use the Query Store.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

goher2000 SSCertifiable Points: 5479 More actions · Answer 5

not sure what you want it for, this is the best you can get, as suggested use extended events..

SELECT getdate() as datetime,
es.login_time,es.last_request_start_time,
ec.client_net_address, es.[program_name], es.[host_name], es.login_name, DB_NAME(eS.database_id) db_name,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK)
ON es.session_id = ec.session_id
GROUP BY es.login_time,es.last_request_start_time, ec.client_net_address, es.[program_name], es.[host_name], es.login_name ,DB_NAME(eS.database_id)
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);