question

bevsz1399 avatar image
bevsz1399 asked

SQL Server Agent will not start

I had an active trigger on my SQL Server 2012 that was checking logins and writing a record to a table. I made a very stupid move and added a field to the table while the trigger was active. I went into the sqlcmd and disabled the trigger. The SQL Agent will no longer start. My SQLAGENT file has the following entries each time I try to start the agent. 2014-02-20 16:53:32 - ? [100] Microsoft SQLServerAgent version 11.0.3128.0 (X64 unicode retail build) : Process ID 1560 2014-02-20 16:53:32 - ? [495] The SQL Server Agent startup service account is S-JTDI\JTDI-DB1$. 2014-02-20 16:53:32 - ? [393] Waiting for SQL Server to recover database 'msdb'... 2014-02-20 16:53:32 - ? [000] 2014-02-20 16:53:32 - ? [101] SQL Server JTDI-DB1\JTDI version 11.00.3128 (0 connection limit) 2014-02-20 16:53:32 - ? [102] SQL Server ODBC driver version 11.00.3000 2014-02-20 16:53:32 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is JTDI-DB1\JTDI 2014-02-20 16:53:32 - ? [310] 4 processor(s) and 8192 MB RAM detected 2014-02-20 16:53:32 - ? [339] Local computer is JTDI-DB1 running Windows NT 6.1 (7601) Service Pack 1 2014-02-20 16:53:32 - ! [298] SQLServer Error: 17750, Could not load the DLL xplog70.dll, or one of the DLLs it references. Reason: (null). [SQLSTATE 42000] (ConnCacheSubsystems) 2014-02-20 16:53:32 - ! [449] Failed to enumerate subsystems (reason: Could not load the DLL xplog70.dll, or one of the DLLs it references. Reason: (null). [SQLSTATE 42000] (Error 17750)) 2014-02-20 16:53:32 - ? [432] There are 0 subsystems in the subsystems cache 2014-02-20 16:53:32 - ! [000] Failed to load any subsystems. Check errorlog for details. 2014-02-20 16:53:32 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) 2014-02-20 16:53:32 - ? [098] SQLServerAgent terminated (normally) I have replaced the xplog70.dll file from a good like copy of SQL Server 2012 and have run the reconfigure commands. Nothing works. Short of reinstalling SQL Server, does anyone have any other advice to fix my issue? Thanks so much.
sql-server-agent
6 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.

Have you tried dropping the column and turning the trigger back on?
1 Like 1 ·
I've got nothing. But the "... or one of the DDLs it references..." doesn't sound good. I'd suggest getting in touch with Microsoft if you can. Otherwise, maybe reinstalling is the answer.
0 Likes 0 ·
thanks Grant. What happens if I replace all objects in the BINN folder?
0 Likes 0 ·
Not something I've tried, so...
0 Likes 0 ·
I have not tried this but I could. I will give this a try after we finish another demo scheduled for next Tuesday. Thanks much!
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Blackhawk-17 avatar image
Blackhawk-17 answered
What results do you get from this: SELECT * FROM msdb.dbo.syssubsystems Non-TSQL subsystems should have the path/file populated and there should be rows returned. If it comes back empty you may need to restore MSDB.
2 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.

Thank you for this suggestion. I did the query in my broke system and compared it to my working SQL Server. I found the only difference in the records was the max_worker_threads field and most records contained a NULL value. I edited the fields to match the entries in the working SQL Server. I started the SQL Agent again and went back to the syssubsystems table and found the fields are once again NULL.
0 Likes 0 ·
Try disabling the Agent and recycling SQL Server. See if MSDB completes its recovery then start the Agent.
0 Likes 0 ·

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.