SQLAgent Memory Usage on SQL 2005

Question

Post reply

SQLAgent Memory Usage on SQL 2005

pammy-331472

SSC Veteran

Points: 248
More actions
October 2, 2006 at 10:25 pm

#192597

Hi there,
    We are planning to upgrade from SQL2000 to SQL2005 in a week time. But we are getting whole bunch of error messages: "There is insufficient system memory to run this query". There are a few schedule tasks that need to be run on the server.
    Our SQL2005 server is using Windows 2003 as OS, 4G RAM, SQL 2005 Standard Edition. We found that the SQLAgent90.exe took 600M-700M memory (at usual time). There's one time that it even goes up to 1.2G. Our DBA never implement SQL2005 into production so we cannot tell that if this is normal for SQL2005.
    Can anyone who'd implement SQL2005 to production please advise? Thanks in advance.
Cheers
Pammy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

sounds unrelated to me although the agent memory seems a tad high. How are you measuring this value?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

pammy-331472 SSC Veteran Points: 248 More actions · Answer 2

Thanks for your reply.

The memory usage reading is done in Task Manager | Processes | SQLAgent90.exe.

Did you implement SQL2005 yet in your company? Is that the normal reading?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

sorry - yes implemented last three clients but not current, will check my own servers tonight.

I'd think that if the agent service was running a batch of commands then memory might go up. What exactly was your agent service running when the memory was high?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

pammy-331472 SSC Veteran Points: 248 More actions · Answer 4

Our server is running a number of tasks (that I suspect might cause problems) on the SQL 2005 server:

- Transaction log backup every 15 minutes.

- WMI alerting to check on server drive's memory space every hour. We are suspecting this is causing the memory leak.

- Send SMTP email whenever needed.

- Some stored proc use linked server.

- .NET assembly to access Active Directory every 30 minutes. We get bunches of error message from here.

- More than 10 nightly job to process data (converted from SQL2k)

We can stop the .NET assembly and WMI (most suspicious). But not sure if the SQLAgent memory will still be high. It tooks about 3 weeks for the memory to go up to that height. But we do not have 3 weeks time to monitor the server since it has to go live next week.