June 25, 2020 at 4:02 pm
Hello,
Is there anyway to check how many connections were open at certain specific time ( not current, say 8:00am in the morning) for a sql server instance/database?
Regards
June 25, 2020 at 4:11 pm
I am using this to get active connections. However that looks like to be current connections, I need connection details which were 1 hour ago..is that possible?
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame
FROM sys.sysprocesses
GROUP BY dbid, loginame
ORDER BY DB_NAME(dbid)
June 25, 2020 at 5:20 pm
As far as I know, SQL Server does not keep historical connection information. You can see current information and it wouldn't be impossible to build up a snapshot system where you store the number of connections at specific times in a table, but as far as I know, there is nothing baked into SQL that does this for you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2020 at 5:25 pm
OK, however I see below does give the connections information which happened a little before, are this not accurate?
select * from sys.dm_exec_connections
June 25, 2020 at 5:32 pm
I am fairly certain those are showing the current connections to SQL Server. You can connect to SQL Server and leave your connection idle for days and it is still an active connection.
If you run:
EXEC sp_who2
You will see that the SPID's in it match up to the SPID's in sys.dm_exec_connections. Both of these are going to give you the current active connections (although sp_who2 will give you the local instance spid's as well).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2020 at 5:57 pm
Interesting! so, if I wait for the connection timeout to happen again which is why I am kind of looking into this, and If I run the select * from sys.dm_exec_connections at that specific time will that count? or what is the best way to capture accurate connection count information when the application is timing out. Now, SQL error log does not have any error for that time, there is no pattern to this, also, not other applications is effected except one. DB's are all online, other users are able to connect.
June 25, 2020 at 7:10 pm
It depends. If the application has a timeout on the SQL connection configured, then the connection will go away when it is closed or timed out. If the application has no timeout configured, the connection will stay active forever. SQL Server has no "timeout" for connections.
Chances are that application has a timeout configured in it for the SQL connection and/or query. If either of those time out, then the application will disconnect from the SQL Instance and will need to reconnect or be restarted.
Seeing timeouts from the SQL side I don't think you will find anything as SQL sees it as a connection logging out. there is no error on the SQL side as everything worked as expected except the client may have disconnected prior to the query finishing its execution.
If you are trying to debug an application level error (connection timeout) from the database side, that is going to be difficult. A much easier approach would be to ask the application team to have a friendly error on the timeout that includes what was being run at the time of timeout. That way you could narrow down what SQL Query is being run and get some execution times on it. Failing that, the application developer could increase the timeout on the SQL query which could be the case with other applications and could explain why only one is having the issue. It also depends on the type of timeout that is happening. Is it a connection timeout or a command timeout? Both can be configured in .NET code and both have different purposes. Command timeout is how long a single query can run for; connection timeout is how long the connection to the SQL instance can remain open for
TL;DR - I think you are trying to fix an application issue on the database side. I'd be looking at the application code for their command timeout and connection timeout values when opening a connection to SQL Server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 25, 2020 at 7:25 pm
Looks like application side. However let me suggest and discuss with app team and see where we go from here. Not much information at this point however queries running from the app user have been shared with the app team. So, queries are not an issue at this point. Application team wants to rule out if it is a database side, which I get it however does not look like an db side issue. Thanks for sharing your thoughts and knowledge, it is more clear to me now!
June 25, 2020 at 8:23 pm
If the app is changing data and is doing it in a transaction, it could be they forgot to put a COMMIT in there. Unlikely, but possible.
The other thing that could be causing application delays would be blocking. If you get multiple people trying to modify data at the same time, the application will need to wait and the users will need to take turns. Depending on how long a data change takes, this could cause timeouts on the application side.
Lets say you have 100 people all entering data at the same time into the same table. Lets say it takes 1 second per insert. That means your longest wait will be 100 seconds. This is an exterme example, but depending on the stored procedure code, it may be doing inserts into multiple tables that need consistency so you open a transaction at the start and take a lock on all of the tables that need to be modified and only releases the lock when they complete. You could be having a blocking issue.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply