Monitoring SQL Agent

Question

Post reply

Monitoring SQL Agent

CrazyMan

SSChampion

Points: 12560
More actions
March 26, 2008 at 4:28 am

#379424

Hi All
I am facing a problem from last week, my server takes large amount of memory, once it hits a maxium amount then it starts to shut down SQL agent, then i have to restart the server, i now wanted to monitor SQL Agent, how can i do that?? i nedd to check wether sql agent is running or not in the server, please suggest any options
Cheers
Suji

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

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

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 1

SQLAgent is a service monitored by Windows. You can set it to restart on issues and even notify you if it's down.

As far as shutting down, how can you tell it's using lots of memory? What is actually happening on the server?

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 2

I agree with Steve, you should first find out what is happening with the server.

My blog

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Hi Steve

Thanks for the reply, the problems with the server seems to be some problems with VM ware, it seems to be virtual machine seems to be runs with lots of memrory and it cant be seen on ESX host, which seems odd, i have asked our VM ware service engineer to have a look into it

Cheers

Sujith

chris-650182 SSC Veteran Points: 240 More actions · Answer 4

You can set it to restart on issues and even notify you if it's down.

How would I set SQL Agent to notify me if it goes down/offline?

I am having a similar problem, but it's an x64 box, no VMWare involved. SQL Agent dies suddenly and the machine runs out of virtual memory. I bumped up the page file but this system has 16gb of physical memory, 8gb is allocated for SQL 2005.

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 5

We had similar issues long time ago. Our task was to monitor if a service was still running. We used Sysinternals PSTools toolkit and created vbscript wrappers around them. This is basically what we did:

1. A VB executable was sceduled on the Windows task scheduler (not as SQLAgent job). This ould execute every few minutes.

2. The program would internally execute the PsList tool and redirect the output to a file.

3. The program would then parse this output file to get information about the presence/absence of the required process.

The good thing about these utilities was that they could be run on a different box and get the information about the server to be monitored, all remotely. This way if the SQL Server went down or went into a hung state, the monitoring script still executed and could report the problem.

This was years ago when SysInternals PSTools was not yet part of Microsoft. They may offer more options now but you will have to check them out. This is the link: http://technet.microsoft.com/en-us/sysinternals/0e18b180-9b7a-4c49-8120-c47c5a693683.aspx

For memory we used Performance Monitor in Alert mode to monitor the CPU Time. Once a threshold was reached, we executed a NET SEND message via the "Run Program on Alert" feature. It was a crude solution but it worked great in the emergency situation we had. Check this site for more info: http://www.windowsitlibrary.com/Content/113/02/2.html.

PSTools may have some more elegant solution for remote memory management but I am not aware of it.