I have got an issue on a database where all of a sudden they are tens of connections opened at the same time. I just review the IIS, located on another server, where the website is running and the time I had the issue nothing could be seen triggered those connections.
Is there a way to log, for a few days, all activities happening on this databases: queries being ran, connections, etc. ?
I'm not a dba-er but have you considered extended events?
Monitor System Activity Using Extended Events - SQL Server | Microsoft Learn
I asked ChatGPT for a suggestion and it came up with the query shown below. After several tries to get it fixed, it didn't worked out.
The issues are Incorrect syntax near 'DB_ID' on the WHERE statement and Incorrect syntax near ')' just before the "ADD TARGET"
CREATE EVENT SESSION [CaptureActiveQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.database_name, sqlserver.client_app_name, sqlserver.session_id, sqlserver.username)
WHERE [database_id] = DB_ID('YourDatabaseName') AND
sqlserver.session_id IN (
SELECT session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName') AND is_user_process = 1
GROUP BY session_id
HAVING COUNT(*) > 20
)
)
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\CaptureActiveQueries.xel')
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);
The WHERE predicate for extended events does not support IN or subqueries:
Just so I'm clear, you're looking for a sudden increase in connections to the database? Or are you looking for connections that are active?
If you need active session monitoring, probably the best way to start is to use sp_whoisactive, and you can capture data regularly to a table for later analysis.
Documentation is here:
Specifically on capturing activity to a table:
Regarding the first scenario (capturing increase in connections), I can't think of a good way to get that except to set an alert for when total connections exceed a threshold. In other words, it won't tell you that 10 new connections were made, only that the total number of connections exceeds 30 or 40 or whatever your threshold is.
That is what I have been searching for. I set up the automated collection of SQL Database connections, as shown here, which is triggered when the number of active connections exceeds a predefined threshold. However, I want to understand why this is happening.
I downloaded the raw log from the IIS and blocked numerous bots, but despite my efforts, I still encounter situations where the number of active connections is unexpectedly high. For instance, the screenshot below depicts that at 5 AM, there were 101 connections to one database, even though the websites have low traffic and it was not during peak hours. I would like to investigate the source of these connections and determine how to block them.
OK, Svetlana's article was exactly what I was suggesting, so you're already covered there.
I don't think there's much you can do from the SQL Server side that would help in blocking them. The connections are coming through your IIS server, so SQL would see them as normal connections from the IIS IP address.
If you know that you should never have more than 60 user connections in total, you can do this:
exec sp_configure 'show advanced',1; reconfigure;
exec sp_configure 'user connections', '60'; reconfigure;
You'll want to leave some connection headroom though, probably 10-20% more connections than your maximum, just to be safe. The goal is to prevent a significantly higher number from a bot storm.
If there's an IIS setting for maximum concurrent sessions, that's another avenue you should consider. Another option is to have your network team check their logs at the switch level, to see if there's an influx from an external source. At a previous company, we found tons of bots originating from AWS IPs, and just blocked an entire range.
You can use that query in place of the one in Svetlana's article, you'd just have to modify your capture table to match the columns returned. The other option is the sp_whoisactive logging mentioned earlier, either one would suffice.
Hi Robert,
First of all thanks for your help.
I have hooked up both, the query shown above and the sp_whoisactive logging, to the event triggered when the number of connections exceeds 200. Wait and see what I'll be getting.