I have a principle and a Mirror server. I have failed over and switched between the two. When I check the Database Mirroring Monitor Job I am getting the following error message:
Message Executed as user: (I have removed the user). Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.
Another user has logged this problem and the suggestions was to stop mirroring and then re-mirror the databases, which then re-create the jobs.
I have done this and it does not work.
I have also also dropped the monitoring job and re-created it. This still gives the same error message.
I do not have spaces, - or any other symbols in the names of the databases. I do have underscores.
The jobs are running as sa.
After I re-mirrored I have noticed that job is created on both Principle and Mirror. But both have the same error.
When I right click the database I am able to register each of the databases for monitoring and am able to monitor using the gui. However if I close down SSMS it doesnt continue to update the monitor until I restart Database Mirroring Monitor and go into View History.