SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent memory issue


SQL Agent memory issue

Author
Message
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 860
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
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2810 Visits: 1623
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 860
thanks for that - but on other servers we can see the memory being released.... thats what looks unusual

MVDBA
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15073 Visits: 14396
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
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 860
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15073 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search