Maint. plan task not update sqlerrorlog

Last Post 22 Sep 2011 12:30 PM by rm. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mddba
New Member
New Member

--
20 Sep 2011 10:00 AM
Hi all,

We have an 05 SP3 STD X64 instance where users can create databases.  Often, they create using SIMPLE recovery.  Because we can't control when new databases are added, we use a subplan that attemps a log backup of all user databases. 

On other 05 servers , if a db is created using SIMPLE recovery, the log backup job will fail AND an entry will be made in the sqlerror log file.  Since we monitor for both failed jobs and errorlog updates, we can detect when a db is created using SIMPLE pretty quick and change it to full.

That's not happening on this server.  The job succeeds (On failure action = Quit the job reporting failure) and no entry is made in the errorlog. 

Can anyone help us get our job to...well...fail and write to the errorlog?

The individual maint. plan text output is as follows:
/********************************/
NEW COMPONENT OUTPUT Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035 Report was generated on "SERVER1". Maintenance Plan: UserDB Duration: 00:00:03 Status: Warning: One or more tasks failed.. Details: Back Up Database Task (SERVER1) Task start: 2011-09-20T12:05:38. Task end: 2011-09-20T12:05:40. Success

Back Up Database Task (SERVER1) Backup Database on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped. Databases: All user databases Type: Transaction Log Append existing Task start: 2011-09-20T12:05:37. Task end: 2011-09-20T12:05:38. Failed0) Database 'DBA_test1' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
 /*******************************/



rm
New Member
New Member

--
20 Sep 2011 12:11 PM
Did you run all tasks in single job? If so, separate them so that failed task can mark job failure. But you should still see log backup failure in sql server log.
wayne
New Member
New Member

--
20 Sep 2011 06:50 PM

-- You could change the base db template so all new dbs will be created with Full recovery.
USE [master]
GO
ALTER DATABASE [model] SET RECOVERY FULL WITH NO_WAIT
GO

--List the dbs as a report or script or job to email yourself
select name dbname,recovery_model_desc,create_date dbcreate_date
from sys.databases s
where s.database_id > 4
order by 3 desc


--You can create a SQL Agent job to run the following to changeChange the dbs to full recovery regardless
USE [master]
GO

declare @sqlcmd as varchar(2000)
declare myc cursor for
select
'ALTER DATABASE ['+name+'] SET RECOVERY FULL WITH NO_WAIT;
'
from sys.databases s
where s.database_id > 4

OPEN myc; FETCH NEXT FROM myc INTO @sqlcmd; WHILE @@FETCH_STATUS = 0 BEGIN
print @sqlcmd;
exec (@sqlcmd);
FETCH NEXT FROM myc INTO @sqlcmd; End; CLOSE myc; DEALLOCATE myc;

gunneyk
New Member
New Member

--
21 Sep 2011 07:22 AM
Yes I wouldn't use a job failing for that either. I would either set a policy to notify you or create a SQL Agent job that selects from sys.databases and warns you of any dbs in SIMPLE mode that shouldn't be.
mddba
New Member
New Member

--
22 Sep 2011 10:40 AM
All good suggestions and thanks for them.

I'll create a local job that will change the DB's to Full where the recovery model is SIMPLE.

As a test, I created the log backup in its own maint. plan but the behavior didn't change.

Can anyone explain why the log backup job succeeds even though, as the individual maint. plan output reflected, a DB in SIMPLE mode was causing the step to throw errors?

If I ran the T-SQL backup log command in SSMS, the backup would fail and an error would be recorded in the sql errorlog file.  Nothing is recorded in the SQL errorlog file when the job created by the maint. plan fails.

Odd...

Again, thanks for all the great feedback.
mddba
New Member
New Member

--
22 Sep 2011 11:26 AM
Additional behavior noted: A DB created in SIMPLE, will not throw an error in the errorlog when a log backup is attempted from a maint. plan created job. That same db will throw an error if the recovery model is changed to full and then back to simple.
rm
New Member
New Member

--
22 Sep 2011 12:30 PM
Sql doesn't add new db to maint plan automatically.
You are not authorized to post a reply.

Acceptable Use Policy