April 27, 2006 at 8:23 am
I have a maintenance plan that backs up my T-Log on an hourly basis. Someone changed the database option to simple thereby causing my job to fail. Looking in the SQL log, the is no error or warning in the log, I was only able to tell by looking at my scheduled jobs. Anybody have any ideas on how to generate the error in the log?
Terry
April 27, 2006 at 8:29 am
Terry,
Is someone changing a database recovery mode an "error" as far as SQL is concerned? It's obviously a mistake on someones part, but i don't think you can raise this as an error (although I'm willing to be corrected)
Paul
April 27, 2006 at 8:36 am
I'll talk to the person who made the recovery mode change, that's human error. What I need is to somehow add the error of the maintenance plan failure. If I look in SQL Server Agent->Jobs, sure enough the job has the red sign and history shows "failed" but nothing logs to SQL server logs. I have ELM monitor for errors on the server and send emails when logged to the event log. Most SQL jobs (though not maintenance plans) log an error to SQL which triggers an error to the event log and I get the email letting me know something is up.
Terry
April 27, 2006 at 8:40 am
hi Terry,
can you not just set the maintenance job to log directly to the windows event log (on the notifications tab, set it to write to the event log on failure) and then your email alert will be triggered.
Hope this helps
Paul
April 27, 2006 at 8:45 am
Sorry, I should've mentioned that it is writing to the event log. However, it goes as a warning. ELM (I don't know enough about this tool, yet) seems to onlypickup errors, according to our infrastructure team.
Terry
April 27, 2006 at 9:10 am
I've added a filter to ELM to trap the warning so I guess this issue is closed. But still, does anyone have any idea why it doesn't log to the SQL logs?
Terry
April 28, 2006 at 6:29 am
Having transaction log backups with the Simple recovery model doesn't make much sense to me since with Simple you're generally making full backups only (since you can only restore/recover from full backups anyhow I think.) Although I can't say that I have ever tried scheduling a transaction log backup with a Simple recovery model database...
If you look at the Transaction log maintenance plan - backup job history does that shed any light? So under EM, Management, SQL Server Agent, Jobs, right-click your backup job and select View job history.. just curious.
If the backup job is failing then you can also setup sqlagentmail to send you an email when the job fails.. I do not love all the sqlagentmail setup requirements- but it should work too..
April 28, 2006 at 6:50 am
The database was originally full. Someone changed it to "simple" during the course of the day (without my knowledge) causing the job to fail. Had I been alerted to the failure, I would've caught the change much earlier. You cannot backup logs from a "simple" recovery database. I know this is a case of "too many hands in the cookie jar" but that's how our organization is right now.
Terry
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply