Backup Error - SQL 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jose - 30 Jan 2008 22:19 GMT

Hi there!
I just posted a question about my Backup Plan 2005 SQL and I got very good
answers!, but now, I'm getting other problem, I'm checking the event viewer
and I see this error message : "BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG
is deprecated. The simple recovery model should be used to automatically
truncate the transaction log"

- I'm using Management Studio to configure the backups
- All my databases are Full Recovery Model.
- I run Full Backup of my databases every day. (1:00am)
- I run every 15 minutes backup of the LOG files

I read something on the MS website (KB 818202) and they say :
"This warning message may be logged because the NO_LOG and TRUNCATE_ONLY
options of the BACKUP statement truncate the transaction log files, and you
might need transaction logs for the full recovery of the database."

So, my doubt is:
- The error is because I'm running the backup of the logs every 15 minutes
and THAT is truncating the LOG file, so, what I have to do? When I'v created
the job on the Management Studio, I havent had the option to set "NO_LOG"
and "TRUNCATE_ONLY"....??

Can somebody give me a hand with this?? please..
What I really want is recovery the full database in case something
happens...at least the most actual data...

Thanks you!

Jose

Reply to this Message

Rubén Garrigós - 31 Jan 2008 00:18 GMT

Check again the options of your log backup maintenance plan. Maybe another
administrator/job/process is doing the truncates. ¿Can you set a profiler
trace to search for "suspicious" log backups with "truncate_only" or
"no_log"?

Signature

Rubén Garrigós
Solid Quality Mentors

> Hi there!
> I just posted a question about my Backup Plan 2005 SQL and I got very good
[quoted text clipped - 30 lines]
>
> Jose

Reply to this Message

Tibor Karaszi - 31 Jan 2008 07:40 GMT

I fully agree. *Somebody* is doing BACKUP LOG dbname WITH TRUNCATE_ONLY (or NO_LOG). You need to
hunt that down.

A regular log backup (where you actually do a backup) will not produce that message in the event
log.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Check again the options of your log backup maintenance plan. Maybe another
> administrator/job/process is doing the truncates. ¿Can you set a profiler trace to search for
[quoted text clipped - 29 lines]
>>
>> Jose

Reply to this Message

Tom Moreau - 31 Jan 2008 13:15 GMT

You may want to set up a profiler trace, and filter on BACKUP in the
TextData.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I fully agree. *Somebody* is doing BACKUP LOG dbname WITH TRUNCATE_ONLY (or
NO_LOG). You need to
hunt that down.

A regular log backup (where you actually do a backup) will not produce that
message in the event
log.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Check again the options of your log backup maintenance plan. Maybe another
> administrator/job/process is doing the truncates. ¿Can you set a profiler
[quoted text clipped - 37 lines]
>>
>> Jose

Reply to this Message

Jose - 31 Jan 2008 15:17 GMT

Thank you!! all of you!
I will tracert the "suspicious" man!

Have a nice day!
Jose

> You may want to set up a profiler trace, and filter on BACKUP in the
> TextData.
[quoted text clipped - 48 lines]
> >>
> >> Jose

Reply to this Message