x

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.

more ▼

asked Feb 20, 2014 at 10:30 PM in Default

avatar image

bevsz1399
21 1 3 7

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.

Feb 20, 2014 at 10:42 PM Grant Fritchey ♦♦

thanks Grant. What happens if I replace all objects in the BINN folder?

Feb 20, 2014 at 10:53 PM bevsz1399

Not something I've tried, so...

Feb 20, 2014 at 10:54 PM Grant Fritchey ♦♦

Have you tried dropping the column and turning the trigger back on?

Feb 21, 2014 at 02:25 AM KenJ

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!

Feb 21, 2014 at 02:33 PM bevsz1399
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 21, 2014 at 02:51 PM

avatar image

Blackhawk-17
12.1k 30 36 42

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.

Feb 21, 2014 at 04:51 PM bevsz1399

Try disabling the Agent and recycling SQL Server.

See if MSDB completes its recovery then start the Agent.

Feb 21, 2014 at 07:30 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x18

asked: Feb 20, 2014 at 10:30 PM

Seen: 1901 times

Last Updated: Feb 24, 2014 at 10:51 AM

Copyright 2018 Redgate Software. Privacy Policy