Bacakup of TRN log will not run anymore

[mb22]

I use to run full database backup of MYDB (92M) and a full TRN log at night..... I got advice to run the TRN logs during the day at specified times... and a full DB backup at night. I modified my maintenance plan to do this.

I also increased the size of my TRN log from 30M to 40M. Now when I run the backups .. this is the error I get. ANy help? I tried changing the 'NT AUTHORITY\SYSTEM' .. to the NT Admin account ...but no good!

The backup used to run before as is! What could I have done wrong? How can I fix it!


Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'MyPC' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan '.NET Backup Full DB & TRN logs' on 7/4/2004 9:54:01 PM
Backup can not be performed on database 'MyDB'. This sub task is ignored.

End of maintenance plan '.NET Backup Full DB & TRN logs' on 7/4/2004 9:54:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

 

[nigelrivett]

You would be better off not using a maintenance plan and coding the jobs yourself. Makes it simpler and you know what it is doing - you can run the commands to test.

Is the database in full/bulk logged recovery mode?
Are you doing a truncate log anywhere?

Try doing a log backup and see what it does. If it works then get rid of the maint plan and schedule the backup.

Also decide whether you need log backups or can do with full and diff backups - that will make the system a lot simpler.

You would usually schedule log backups to recur - hourly for systems that aren't very active but often more frequently. Think about how often you need them.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mb22]

Thanks.... it looks like I had the truncate log set to TRUE. I did a dbcc shrinkfile(2) ..... of my log .. and forgot to set it back to FALSE after that .....

when i set it back to false the LOG backup run successfully thanks.

I think the diff backup is something to consider as you said? ... but my log only takes about 3 seconds to backup ... so it not bad for 2 hour intervals during the day from 10am-10pm , isn't it? Or you still think a DIFF backup will be useful.

I haven't done any diff backup but I can research more on it if you think it can help in my situation.

thanks again.