question

SQLserverlover avatar image
SQLserverlover asked

SQL server agent not starting

Please help : I am trying to restore msdb database backup taken in SQL server 2008 R2 (SP2) to SQL server 2016. Database restore completed successfully however SQL server Agent is not starting. Agent error log: 2018-04-21 07:29:18 - ? [100] Microsoft SQLServerAgent version 13.0.4001.0 (X64 unicode retail build) : Process ID 9264 2018-04-21 07:29:18 - ? [495] The SQL Server Agent startup service account is ********************* 2018-04-21 07:29:18 - ? [393] Waiting for SQL Server to recover database 'msdb'... 2018-04-21 07:29:18 - ? [000] 2018-04-21 07:29:18 - ! [165] ODBC Error: 0, Invalid character value for cast specification [SQLSTATE 22018] 2018-04-21 07:29:18 - ? [101] SQL Server 0 version 13.00.4001 (0 connection limit) 2018-04-21 07:29:18 - ? [102] SQL Server ODBC driver version 14.00.500 2018-04-21 07:29:18 - ? [103] NetLib being used by driver is ****; Local host server is 2018-04-21 07:29:18 - ? [310] 4 processor(s) and 8192 MB RAM detected 2018-04-21 07:29:18 - ? [339] Local computer is ******* running Windows NT 6.2 (9200) 2018-04-21 07:29:18 - ! [000] SQLServerAgent cannot start because the msdb database is read-only 2018-04-21 07:29:18 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) 2018-04-21 07:29:18 - ? [098] SQLServerAgent terminated (normally) Note: msdb database is in read write mode
sql-agent
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think this is entirely down to the msdb being restored from a different version of SQL Server. @SQLserverlover - Why were you doing this? What were you trying to achieve?
1 Like 1 ·
+100 Excellent question @ThomasRushton
1 Like 1 ·
I'd check first to see if the files on the operating systems are not set to read only.
0 Likes 0 ·
HI JohnM, I have checked that already. Files are not set to read only mode Thanks for ur reply
0 Likes 0 ·
Did you check to see what compatibility level the database is set to? By default, it will remain at 100 (2008) so 2016 might be looking for something specific related to the 2016 level of 130.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.