SQL Agent - SQLSTATE 21000 error

  • 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?

  • Hi,

    What is the version of SQL server and OS ?

    Regards,
    Shivrudra W

  • SQL 2008 , Windows Server 2003 Standard x64

  • Anyone... :crying:

  • 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?

  • 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.

  • 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.

  • 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.

  • 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:

  • 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?

  • 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

  • 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...'

  • 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! 😀

  • 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

  • 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