Two nights in a row, during the same 1-hour time frame, something shut off SQL Server Agent in my SQL Server 2000 box. I noticed this because all of jobs were failing…so when I looked into the box, the SQL Server Agent service had been stopped.
There is no mention of it stopping in the logs and there is not a lot of info that seems to be related in the Windows event logs either.
It is as if something did not like it and disabled it without leaving a trace.
The “server” people seem to think it is my issue and there must be something in the database doing it. I think unless I make a SP to turn it off or dial in and shut it off, that it cannot do it from within the database.
Has anyone else had this type of issue occur?
I have had a few issues in the past where a job or two do not fire due to invalid user name and password but I attributed it to a security script or process running outside the box that for whatever reason did not recognize the Agent but to actually come in and turn it off…two nights in a row…at the same time each night…
I don not know what to make of this.
The only items in the Windows logs are for back ups of the drives and system files on the server but not the database files. Other than that, there is a security script being run around the same time but that is from the security people, not me.
Also, last time something like this happened, SQL Agent did not get shut down. I have experienced the occasional “Job Failed because the user privileges are insufficient” or something like that but never have seen SQL Agent get turned off.
It is as if something turned off the SQL Server Agent Windows Service it self. There is no trace at all in any of my logs of anything touching Agent.
All I can see is that there were some back ups going on (system files, not SQL backups) and some script was running but I have been “promised” nothing has been changed in the scripts or group policies.
IF something within SQL can turn off Agent, wouldn’t I be able to find a trace of it somewhere?
Maybe worth mentioning that after a re-boot a few months ago(presumably after a Windows update or patch) when the SQL came back up, something had set a database to be AUTOCLOSE ON so every time something called the database, there is a log entry saying: Starting Up Database”XYZ”.
I see no reference to locks, no memory page failures nothing.
The actual errors the packages gave after the Agent was turned off were these:
First Job run after Agent turned off;
Executed as user: serverdomain\serveruseraccount. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 5105 (13F1) Error string: Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\XYZ_Data.MDF' may be incorrect. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
And this one came up and was the only error from then on.
Executed as user: serverdomain\serveruseraccount. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 4064 (FE0) Error string: Cannot open user default database. Login failed. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed