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.

SQL Server 2008 Service Weirdness
  • Hhe article's content rich variety which make us move for our mood after reading this article. surprise, here you will find what you want! Recently, I found some wedsites which commodity is colorful of fashion. Such as http://www.always11.net that worth you to see. Believe me these websites won’t let you down.

  • RJ

    We ran into a similar problem to #2; on our SQL server (not sharepoint) the SQL Server VSS Writer was always set to manual. When switching AV programs from McAfee, the McAfee deinstall started the service and it switched to automatic. That ended up screwing our backups (both windows server backup and backup exec could not back up the disks). Once we located a reference to that service as the culprit we added pre-post batch command to stop and start the service so backup could work.

    Later we found the event log entry about hte service startup switching to automatic, so we set it back to manual. Every morning when we checked it was back to automatic.

    It turns out that the NET START command used in the post batch file both STARTS the service AND switches it to AUTOMATIC start. I could not find this behavior documented but that is what happened on our box. So we removed the post batch command, reset the service to manual, and so far its ok, at least I assume until some other process starts that service for its own use. We’re leaving the pre-batch that stops the service in place as a safety precaution now.