MS SQL Server - SQL Server 2005 Agent Memory Leak

Author

SQL Server 2005 Agent Memory Leak

Viracochas

2006-10-24, 6:28 pm

The setup:

I have a server with two SQL 2005 instances. One instance has 4
mirrored databases, the other has 6 databases that are mirrored.

I recently added some alerts as described at the end of this article:

http://www.microsoft.com/technet/pr...ringevents.mspx

So each database has 17 WMI based alerts (one for each status and 4 for
performance).

For the alerts that occur due to manual or automatic failover I have
set them to also execute a job as a repsonse. This job fires off a job
on the mirror server (via a linked server registration) to resynch the
logins when the database has failed over.

There is also a place holder job step to run a command shell in
preparation for a shell script to fail over the application servers
(for those apps with non-mirror aware connections). This place holder
job step currently appends a dir command output to a text file.

Behaviour

What I am seeing now is that the SQL Agents for both instances have a
working set that is increasing by 5k and 9k a second. This increases
until both agents are utilising around 1-1.5Gb or ram each (on a server
with 4Gb of ram).

As soon as I restart the agent services, the memory usag begins to
climb - it appears to not be related to

Furthermore, The SQL Server 2005 instance with the most alerts (since
it has 6 databases rather than 4) also has the agent with highest
memory usage (currently 83Mb compared with 58Mb).

If I disable the alerts then the memory usage stops growing at a high
rate - the increase either slows down or is even released.

Does anyone have any experience with this, or any idea of a solution?

Regards

Vira

jgrine

2006-10-24, 6:30 pm

Looks like I've got the same thing. One instance with 5 mirrored dbs. Had a
memory leak in a clr sp, so I've been watching memory pretty close since
then. Just noticed today that sql agent keeps growing. I've got one alert
that catches all statuses and runs a job that passes the status to a stored
proc. My leak is much smaller, so it makes sense that it is the alert
system. You might try combining the status alerts into one alert if it's
possible for you. Should at least slow down the leak.

"Viracochas" wrote:

> The setup:
>
> I have a server with two SQL 2005 instances. One instance has 4
> mirrored databases, the other has 6 databases that are mirrored.
>
> I recently added some alerts as described at the end of this article:
>
> http://www.microsoft.com/technet/pr...ringevents.mspx
>
>
> So each database has 17 WMI based alerts (one for each status and 4 for
> performance).
>
> For the alerts that occur due to manual or automatic failover I have
> set them to also execute a job as a repsonse. This job fires off a job
> on the mirror server (via a linked server registration) to resynch the
> logins when the database has failed over.
>
> There is also a place holder job step to run a command shell in
> preparation for a shell script to fail over the application servers
> (for those apps with non-mirror aware connections). This place holder
> job step currently appends a dir command output to a text file.
>
> Behaviour
>
> What I am seeing now is that the SQL Agents for both instances have a
> working set that is increasing by 5k and 9k a second. This increases
> until both agents are utilising around 1-1.5Gb or ram each (on a server
> with 4Gb of ram).
>
> As soon as I restart the agent services, the memory usag begins to
> climb - it appears to not be related to
>
> Furthermore, The SQL Server 2005 instance with the most alerts (since
> it has 6 databases rather than 4) also has the agent with highest
> memory usage (currently 83Mb compared with 58Mb).
>
> If I disable the alerts then the memory usage stops growing at a high
> rate - the increase either slows down or is even released.
>
> Does anyone have any experience with this, or any idea of a solution?
>
> Regards
>
> Vira
>
>

Viracochas

2006-10-24, 6:33 pm

MS have confirmed that there is a known bug in the SQL Agent Code for
WMI alerts that is being worked on inclusion in SP2.

jgrine wrote:[color=darkred
]
> Looks like I've got the same thing. One instance with 5 mirrored dbs. Had a
> memory leak in a clr sp, so I've been watching memory pretty close since
> then. Just noticed today that sql agent keeps growing. I've got one alert
> that catches all statuses and runs a job that passes the status to a stored
> proc. My leak is much smaller, so it makes sense that it is the alert
> system. You might try combining the status alerts into one alert if it's
> possible for you. Should at least slow down the leak.
>
> "Viracochas" wrote:
>

Sponsored Links