Dealing with an issue where our SQL Agent isn't always running on SQL 2005. The event and agent logs simply show -
2008-07-12 12:18:17 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS... 2008-07-12 12:18:19 - ? [098] SQLServerAgent terminated (normally)
Looking through the rest of the logs, it appears SQL Server itself also shut down and restarted automatically around this time (This is the middle of the day, so I'm surprised we didn't hear anything from users if we had a problem.)
2008-07-12 12:18:16.93 spid56 Setting database option OFFLINE to ON for database BEDB. 2008-07-12 12:18:17.02 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 2008-07-12 12:18:17.04 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 2008-07-12 12:18:17.04 spid56 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 2008-07-12 12:18:17.26 spid56 Starting up database 'BEDB'. 2008-07-12 12:18:17.44 spid56 CHECKDB for database 'BEDB' finished without errors on 2008-06-29 00:17:07.647 (local time). This is an informational message only; no user action is required. 2008-07-12 12:18:19.30 spid55 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. 2008-07-12 12:18:19.30 spid55 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. 2008-07-12 12:18:19.30 spid55 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. 2008-07-12 12:18:20.82 spid12s Service Broker manager has shut down. 2008-07-12 12:18:21.66 spid4s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required. 2008-07-12 12:18:21.66 spid4s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Regardless, this is one example with the server service restarting. I can't say whether that's the norm. But we took SQL offline this weekend to run all the Microsoft patches and such we could find for SQL (Was already at the latest SP level.) When we completed our work Saturday, we made sure the SQL agent was running. This morning - not running.
It looks like it shut down shortly after a transaction log backup occurred. . .
I see a lot of SQL Agent succesful starts in the event logs, but not one for every time the service shuts down. I'm the systems guy, not a big SQL guy - fyi.
