SQL Agent memory issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Agent memory issue

SQL Agent memory issue

Rate Topic

Display Mode

Topic Options

Author

Message

MVDBA

Posted Wednesday, September 05, 2012 3:37 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

Hi

I have a bunch of(6x) 64 bit sql 2008 (not R2) standard edition servers that seem to be leaking memory

the sql agent process is ramping up to 3GB in a single day (approx 40Kb per second) (note this is sqlagent.exe NOT sqlserver.exe)

the SQL agent is quite busy (29 jobs on 15 minute schedules)

Jobs simply perform remote proc calls on a linked server then wait for a specified period and then loop (until a specified amount of time has elapsed)
if i stop all of the jobs and disable them then the memory is not released.

also there is no SSIS use or CLR use. purely TSQL

i found the following article

http://support.microsoft.com/kb/971622

i'm not using sql variants and all of my servers are on sp3 (well above sp1 cu8)

Has anyone seen this before ? or have a fix?

MVDBA

Post #1354403

Robert Davis

Posted Thursday, September 06, 2012 12:33 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 1,560, Visits: 1,408

Doesn't sound like a memory leak. You're over-working SQL Agent running linked server queries (which do not use the buffer pool) and it's requiring 3 GB of memory. I would not expect SQL Agent to release memory it has taken even if it's not currently all in use.

My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #1355048

MVDBA

Posted Thursday, September 06, 2012 2:52 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

thanks for that - but on other servers we can see the memory being released.... thats what looks unusual

MVDBA

Post #1355127

opc.three

Posted Thursday, September 06, 2012 12:33 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

Same Windows and SQL Server Build # on all servers?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1355558

MVDBA

Posted Friday, September 07, 2012 2:55 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

yes all of them - originally i posted 6 servers, but it's actually 18.. the other 50 or so servers (all win 2008 datacentre with sql 2008 sp3) seem to run at about 18Mb

MVDBA

Post #1355831

opc.three

Posted Friday, September 07, 2012 4:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

Well something must differ between the servers. I am thinking code now. Are they running the exact same set of jobs?

Relevant link?

http://blogs.us.sogeti.com/sqlserver/monitor-sql-agent-memory-consumption/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1355887

« Prev Topic | Next Topic »

Permissions