January 11, 2010 at 4:14 am
I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as follows
[298] SQLServer Error: 512, Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000]
Strange thing is I cannot find the process which is causing this error, I have even disabled all SQL Agent jobs and restarted the service and server eventually but it still produces this error message every 30 seconds.
Anyone come across this before?
January 11, 2010 at 4:23 am
Hi,
What is the version of SQL server and OS ?
Regards,
Shivrudra W
January 11, 2010 at 4:39 am
SQL 2008 , Windows Server 2003 Standard x64
January 12, 2010 at 7:30 am
Anyone... :crying:
February 8, 2010 at 10:19 am
Hi, just want to bump this post again. I'm still having this issue and no luck with it so far. Has anyone experienced this with the SQL Agent?
February 9, 2010 at 1:39 pm
Two places to look
1) Check your triggers. This error may be a 'side effect' rather than directly in a SQL Agent job
2) Run profiler to see if you can isolate the transaction that's causing the problem.
And, finally, one question: When you disable the SQL Agent process, but leave SQL Server running, do you see this problem in the logs?
Steve G.
February 10, 2010 at 8:12 am
Hi Thanks for that advise, I have disabled the only trigger I have on the database and have run a trace in sql profiler for error logs, SQL batch exec ect but it hasn't turned up anything. Are there any specific events you recommend I should check in the profiler.
February 10, 2010 at 9:08 am
One thing to consider: Since you're not finding this where you expect (SQL Agent jobs) then perhaps it's somewhere else. Is this a production database or a development system?
As for profiling, I'd track all T-SQL transactions regardless of whether they're Agent jobs or not.
One last question: when you completely disable the SQL Agent service, do the errors go away?
Steve G.
February 10, 2010 at 10:48 am
This is a production database, the errors appear in the SQL Server Agent log only and not in the normal Sql Sever log so naturally when I stop the Agent the the problem no longer appears in the log as the Agent is not logging anything.
This has been a really tricky one, I have tried to replicate it on the staging/test server but there it works fine, no errors. On the productions server I have stopped every process I can think of other that stopping the SQL Server and monitored the log but they still appear :unsure:
February 11, 2010 at 5:59 pm
I'm assuming from your earlier comments that you can't determine the job name or id from the log file? Which version of SQL Server are you using?
February 12, 2010 at 1:15 am
Run a standard trace and write to a table. Once the error occurs check the table around that time to see what is going on. You could also extract which objects use subqueries and start to narrow them down. If all else fails run a trace returning everything and narrow it down and start and end time
February 12, 2010 at 4:50 am
The only thing I have found running a standard trace is that SQL Agents runs stored proc about every half-minute called msdb.dbo.sp_sqlagent_get_perf_counters and it also runs a select statemen... SELECT N'Testing Connection...'
February 12, 2010 at 5:03 am
I did a bit of reading on google about sp_sqlagent_get_perf_counters and then deleted the demo alerts from the alerts folder in the SQL Agent and that seemed to have solved the problem! 😀
February 12, 2010 at 9:57 am
Hi,
Can you run DBCC on MSDB database and see if you are getting any error(s)? To me is looks some kind of Corruption is system tables.
Deepak
September 11, 2012 at 2:36 pm
Hi ,
Are you able to solve this problem ? i found this same error message in my production Environment .
Please share the knowledge if you have or anyone have solved this problem
Thanx
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply