Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later
“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 but it never start and reported the same error again and again.
To fix SQL Server error 14258, I looked into SQL Server advanced server configuration option where i saw that SQL Server was running into fiber mode scheduling because Lightweight pooling option was set to 1.
Lightweight pooling option provide a means of reducing the system overhead associated with the excessive context switching sometimes seen in symmetric multiprocessing (SMP) environments. Setting lightweight pooling to 1 causes SQL Server to switch to fiber mode scheduling. The default value for this option is 0.
Fiber mode is intended for certain situations in which the context switching of the UMS workers are the critical bottleneck in performance. Because this is rare, fiber mode rarely enhances performance or scalability on the typical system.
The lightweight pooling option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change lightweight pooling only when show advanced options is set to 1. The setting takes effect after the server is restarted.
I set lightweight pooling option back to the default value by running below T-SQL code.
sp_configure 'show advanced options', 1 go reconfigure go sp_configure 'lightweight pooling', 0 go reconfigure
And after this change, my issue about error 14258 got resolved.
Read more about Server Level Configuration Options:
- How to change network packet size in SQL Server?
- How to limit number of user connections in SQL Server?
- Set MIN & MAX Memory to SQL Server
- Learn SQL Server Checkpoint Process
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021