Sql-server – SQL Server agent Error

sql-server-2005sql-server-agent

I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7.

Event Type: Error
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine 
Event ID:   318
Description:
Unable to read local eventlog (reason: The event log file is corrupted).

Event Type: Information
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine 
Event ID:   311
Date:       2/22/2011
Time:       6:52:44 AM
User:       N/A
Computer:   XXXXXXX
Description:
Attempting to re-open the local eventlog...

Event Type: Warning
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine 
Event ID:   312
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been missed.

Then, the server shut down unexpectedly. The shutdown stopped the events. But, my concerns are: What was happening? Does anyone know what this means? More importantly, what kind of measures do we need to take to prevent this from happening?

Best Answer

Sounds like you've got some sort of corruption problem on the disk which the SQL Agent logs get written to. I'd check the disk for corruption, as well as the C drive in case it's talking about the application log which the SQL Agent also writes to.

Related Solutions

Sql-server – SQL Server Agent

You need access to SQL Server Agent in order to do what you need to do. This is done through msdb permissions: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

You can create a job to adhere to your schedule, and then the job will execute whatever task it is that you need done.

As for the performance impact on the server, SQL Server Agent itself is negligable but whatever the job does will definitely be a consideration on when it is run, depending on how expensive the job is. To give a better estimate on server impact, please tell us exactly what you need the job to do.

Sql-server – Reset / force SQL Agent Next run date

After the good work done by Aaron, I found that executing the result from my ReloadJobSchedules column below did the trick. For some reason I couldn't get the schedule to refresh to the correct next run date by manually updating msdb.dbo.sys.sysjobschedules.next_run_date .

SELECT  j.name AS JobName
        ,sch.name AS ScheduleName
        ,'EXECUTE msdb..sp_update_jobschedule @job_name = ''' + j.name + ''', @name = ''' + sch.name + '''' AS ReloadJobSchedules
FROM    msdb.dbo.sysjobschedules s
INNER JOIN
        msdb.dbo.sysschedules sch
        ON  s.schedule_id = sch.schedule_id
INNER JOIN
        msdb.dbo.sysjobs j
        ON s.job_id = j.job_id
WHERE   sch.[enabled] = 1       
ORDER BY
        j.name

Best Answer

Related Solutions

Sql-server – SQL Server Agent

Sql-server – Reset / force SQL Agent Next run date

Related Question