When reviewing my daily monitoring information this morning I noticed I did not get any emails from three different servers for one of my projects. You see, we have scripts we use to look at backups and when they were last performed, we look at job failure, we look at, well you get the picture. We monitor our servers.
I started to investigate the issue. Before I looked at the job history to see if there were any errors I looked on the server to see if the backs had actually been done, regardless of what the results I was reviewing. Yes. The databases and logs were/are being done.
When I looked at the history for the monitor Job the following error message is what I saw.
Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).
The step failed.
I also noticed the job had been failing the last two days. (I was off the last 2 days so I didn’t notice it before today, just in case you were asking why it took so long to discover)
I ran the code in the query window and it executed correctly. Hmm. What has changed since the errors started? “Light bulb!”
I added domain accounts last week to run the services. OK, what could be wrong then?
All the services are running. No errors there. What else? Looking at the error message again the job was executed under the domain account I added, so that is right. How about permissions? Ah ha! Permissions. I forgot to add the SQL Agent domain account to SQL Server and assign the proper permissions. Fixing that by adding the domain account to the server and granting admin privilages worked.
So, hopefully, you learned a valuable lesson from my mistake.