Is there a way to start SQL Server Agent after the server reboots? I am thinking of using a task scheduler job and command line. But is there an option where I can just check on so that it auto start? Thanks
Isn't it a service? Set it so that it auto restarts....in SQL server configuration or windows services
Right-click on SQL Server Agent - select Properties.
Note: this is one of those items related to the installation of SQL Server where I see a lot of people miss. The default installation is set to manual and if not changed during the installation of SQL Server will set the service to manual.
Make sure that you set to the "With Delay" version of automatically starting. It'll save on a lot of startup errors.
This should not be necessary - the SQL Server Agent service is set with a dependency on SQL Server so it cannot start up until after SQL Server has started anyways - which will force a 'delay' on the agent service anyways.
Heh... understood on "This should not be necessary" but... I've found that it frequently is. I also delay the start of SQL Server for the same reason.
It's not because of anything that SQL Server is doing. It's because it sometimes takes a bit of time for mapped drives to settle. If you don't have any mapped drives then, as you say, it shouldn't be necessary to delay the start of either SQL Server or SQL Server Agent. It also doesn't hurt anything if you do delay them and so acts as a bit of bullet-proofing for any future changes.
I saw issues several years ago - where the storage would not present the drives and bring them online in a timely manner and SQL Server would not start. On a non-clustered system SQL Server will not be set with dependencies on the drives - which could then cause issues with starting up...
For that situation - we modified the startup to 'Automatic (Delayed Start)' and added dependencies for the drives. But SQL Server Agent didn't need to be modified because it was already 'delayed' because of its dependence on SQL Server.
Once the server/storage team resolved the issues with drives not coming online in a timely manner - we never saw that issue again and haven't needed to worry about delayed starts.
In a cluster - you have to set drive/volume dependencies for SQL Server, so that is a non-issue.
We did similar but the drives that took the longest to "attach" were drives on other boxes where we got the data to import from. It was necessary for us to delay SQL Agent because that's where the jobs for the imports lived.
Like I said, it costs comparatively little time to delay the start of SQL Agent but it saves a lot if something changes. If you don't want to add that layer of bullet-proofing, then don't but I'll always recommend that you do.