SQL Server 2008 Service Weirdness

Two weird things I learnt about SQL Server while building the SharePoint 2010 Service Manager.

1. SQL Server Agent service for SQL Express is bogus

Whenever Service Manager started, the SQL Server Agent service for the SharePoint (SQL Express) instance  would immediately stop with either of the following errors logged in the Windows Event Log.

• The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
• SQLServerAgent could not be started (reason: Error creating a new session).

The self explanatory title of the bug filed on Connect says it all ‘SQL Express installs SQL Agent Service for no apparent reason’. Apparently the team cutting down features for the Express edition forgot to tell the Agent team that they weren’t needed in Express.

2. SQL Server VSS Writer Service : Startup Type get’s reset to ‘Manual’

The Service Manager has a feature that let’s you set the startup type of all the SharePoint and related services to Manual. This way they wouldn’t automatically startup when Windows starts hence leaving the workstation to boot faster.

The Service Manager only shows the ‘Stop Automatic Startup’ button if the Startup Type of any of SharePoint services are set to Automatic. While testing the feature I released that after sometime the button automatically showed up even after I had set all the services to start manually.

That was when I figured out that even if I manually change the service (through Control Panel) to start manually, something would change the startup to Automatic after a while. I haven’t figured out what changes it’s startup type to automatic but I’m guessing that’s by design. My workaround for the Service Manager was to ignore the startup type of the VSS Writer service when checking if all the services were set to manual.