Do you have startup procedures? In some scenarios we might be having some startup procedures in our SQL Server system and we might face some issue during starting SQL server. So for troubleshooting purpose we might need to bypass the startup procedures so that SQL Server don’t need to grant worker thread for each procedure.
You need to add trace flag 4022 to startup parameter in SQL Server and start SQL Server it will bypass all startup procedure. This trace flag is really helpful during SQL Server startup issues, you can enable the trace flag so that it skips all startup procedure. Let’s do a test on this, I have a startup procedure called as “startup_proc”. From the image below you can find that the procedure is set as startup and you can find it’s launched during SQL Server startup.
Now I’ve enabled the trace flag 4022 in startup parameter and restarted SQL Services , now you can find from the image below that the startup procedures are skipped. However I couldn’t find any entry in errorlog for this trace flag but it skipped launching of startup procedures.