Friday, February 18, 2011

"Error 14258: Cannot perform this operation while SQL ServerAgent is starting. Try again later."

I saw this error today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync.i checked log but i didn't find anything even Sql agent was running and all jobs was showing as successful.

Then i tried to bounce Agent service on primary node then i got below error:

"Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later."

To fix this issue, I tried stopping and then restarting just the Agent
service and it won't start, reporting the same error then my guess was that SQL Server was running in lightweight pooling mode. I set it back to the default of
thread mode.

sp_configure 'allow updates', 1
reconfigure with override
sp_configure 'lightweight pooling', 0
reconfigure with override

And after this change it worked for me..

Wednesday, February 9, 2011

How to disable Auto Commit in SQL Server

You can turn auto commit OFF by setting implicit_transactions ON:


When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.
And when you need to enable it just run above cmd with OFF clause.


autocommit is the default for Sql Server 2000 and up.