One of my clients asked assistance in fixing an interesting issue. They informed me that they are not able to run any job in SQL Agent. When they try to run the job manually, they are seeing below message related to SQLServerAgent .
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot perform this operation while SQLServerAgent is starting. Try again later. (Microsoft SQL Server, Error: 14258)
My initial search showed me below KB BUG: DTC Transactions May Fail When SQL Server Is Running in Lightweight Pooling Mode.
But above is not applicable as we were running SQL Server 2012 and fiber mode was not enabled. I went ahead and asked SQLAgent.out file to see if there is something interesting
2016-07-21 16:22:49 – ? [297] SQLServer Message: 15457, Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2016-07-21 16:22:50 – ? [100] Microsoft SQLServerAgent version 10.50.4000.0 (x86 unicode retail build) : Process ID 3748
2016-07-21 16:22:50 – ? [101] SQL Server IND-SAP-SQL version 10.50.4000 (0 connection limit)
2016-07-21 16:22:50 – ? [102] SQL Server ODBC driver version 10.50.4000
2016-07-21 16:22:50 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is IND-SAP-SQL
2016-07-21 16:22:50 – ? [310] 2 processor(s) and 2048 MB RAM detected
2016-07-21 16:22:50 – ? [339] Local computer is IND-SAP-SQL running Windows NT 5.2 (3790) Service Pack 2
2016-07-21 16:22:52 – ! [364] The Messenger service has not been started – NetSend notifications will not be sent
2016-07-21 16:22:52 – ? [129] SQLSERVERAGENT starting under Windows NT service control
2016-07-21 16:22:52 – ? [392] Using MAPI32.DLL from C:\WINNT\SYSTEM32 (version 1.0.2536.0)
2016-07-21 16:22:52 – ? [196] Attempting to start mail session using profile ‘SQLSrvrSvc’…
When I checked on my machine, my last line was not same. So, it looks like MAPI32.dll is being used. I checked further and found that they were using SQLMail which uses MAPI client to send email. We changed the setting to use Database Mail as shown below.
Once we changed the mail profile to Database Mail, we were able to run the jobs manually.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Hi Pinal,
I have one question in my mind. Please help me out to figure out it.
When we create clustered index on table then it will automatically create primary key on that column of the table. Am i right? So according to this statement, we can not inserted duplicate record into the table. Now i am inserting duplicate record into the table. Just wondering, I am able to insert duplicate record into the table. How it is possible.
Example:
create table t1
(
id int
)
create clustered index ix_id on t1(id)
insert into t1 values(1),(2),(3),(4)
select * from t1
insert into t1 values(1),(2),(3),(4)
select * from t1
drop table t1
Please me to correct this.
Regards,
Neha
Clustered index do not create primary key by default .U need to create one to avoid duplicacy in ur records..
But if u create Primary key and there are no clustered index already then it will create one by default ..
I tried everything mentioned in other forums but nothing worked .
Finally I tried the following :-
1. Removed the node by “Remove Node” from Fail over cluster on the passive node
2. Reboot the passive node
3. Stop the SQL Service and the SQL Agent Service on Active node
4. Start the service back
5. SQL Agent started working fine