3

In a recent monitoring session I noticed that one of our main MSSQL 2014 databases had an increasing number of open connections.

It started with more than 1000 open connections, and now after two weeks it has increased to more than 2600. One would think that this would be improper connection handling in one of our applications, but out of the 2610 open connections, 2600 belong to SQLSERVERAGENT. When inspecting the result of sp_who2, almost all SQL Server Agent connections are program name 'SQLAgent - Temporary Worker', sleeping and 'awaiting command'.

I've searched for threads on improper behavior on the SQL Server Agent side, but almost all results talk about applications not closing their db connections properly. It seems to me that the jobs performed by the SQL Server Agent are not correctly being closed?

5
  • 2
    What agent jobs are scheduled or run recently? Commented Apr 7, 2015 at 14:10
  • 2
    What CU are you running SQL Server 2014 on ? This problem occurs when you have SQL Server Managed Backup turned on and is fixed in CU6.
    – Kin Shah
    Commented Apr 7, 2015 at 14:57
  • @Kin nice find! Commented Apr 7, 2015 at 18:45
  • @Kin Oh, wow. I've searched endlessly on all possible pointers and did not find that fix. I'm going to investigate whether this will fix it; if you can turn your comment into an answer I will upvote it, and mark it should it indeed fix my problem!
    – Kazu
    Commented Apr 8, 2015 at 7:17
  • @Kazu Glad that it helped you. I have put it as an answer.
    – Kin Shah
    Commented Apr 8, 2015 at 13:33

1 Answer 1

2

KB 3034297 - Cumulative Update 6 for SQL Server 2014 describes the issue that you are encountering and is fixed in SQL Server 2014 CU6

When you turn on SQL Server Managed Backup in Microsoft SQL Server 2014. When you let SQL Server and SQL Server Agent run for several days, there are many connections with the program name SQLAgent - Temporary Worker. They are created by "sqlagent.exe," and cannot be closed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.