October 24, 2008 at 7:40 am
The Gurus,
This error message "2008-10-24 00:00:00.29 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log." appears daily on my SQL Server log.
What can I do about this? Any idea of why its occuring and how it could be fixed?
Many thanks for your prompt response.
Regards,
Ola.
October 24, 2008 at 7:56 am
something is issuing either a backup log with truncate only or backup log with no_log against one of your databases. This means the log is being truncated without the contents being saved thus breaking your recovery chain, so you better check this is what you want done,
Its not an error so you don't need to worry on that score but as the message says these commands will be going at sometime and only remain for backward compatibility, Microsoft are just letting you know that. They are replaced by putting the databases in simple mode. I don't know why they have done this, no_log can be a life saver in extreme circumstances.
---------------------------------------------------------------------
October 24, 2008 at 12:06 pm
sahoong (10/24/2008)
The Gurus,This error message "2008-10-24 00:00:00.29 Backup BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log." appears daily on my SQL Server log.
Looks like there's a job at midnight that's including a BACKUP LOG ... WITH TRUNCATE_ONLY statement in it. Be aware that this breaks the log chain and if your DB fails some time the next day, you will not be able to restore to a point in time, unless you have taken a full backup afterwards. Is the potential loss of several hours of data acceptable?
How often do you run full backups? How often are you running transaction log backups? If the drive that the database is on fails, how much data loss is acceptable?
I don't know why they have done this, no_log can be a life saver in extreme circumstances.
Because PSS was getting a lot of calls where people had run that without realising the consequences and then couldn't restore their databases fully after a disaster. In SQL 2000, running that didn't prevent you from running log backups afterwards. The log backups were completely useless, but they ran without error.
It's not as if the functionality's completely gone. You can achieve exactly the same effect by switching the DB to simple, running a checkpoint and then switching back to full. It hopefully will make people a little more aware what they're doing though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 1:59 pm
Gail, the situation I am thinking of is when there is no drive space available anywhere and the log is so full even a truncate_only won't work, then the no_log was required.
Seems to me putting the database in simple mode and issuing a checkpoint will require an entry made in the log, so there is potential for situations where this will fail, and when no_log goes what then?
or has some undocumented change been made that this situation can no longer arise?
---------------------------------------------------------------------
October 24, 2008 at 3:06 pm
As far as I know, all backup logs (including the no_log) have to write into the tran log to indicate that a backup was done. I don't know if it was different in earlier versions, but in SQL 2005, No_Log and Truncate_only are synonyms and behave exactly the same (at least BoL says they do)
I know I've been in a situation (SQL 2000) where the log and drive were so full that nothing worked, not truncate_only, not no_log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2008 at 5:01 am
GilaMonster (10/24/2008)
As far as I know, all backup logs (including the no_log) have to write into the tran log to indicate that a backup was done. I don't know if it was different in earlier versions, but in SQL 2005, No_Log and Truncate_only are synonyms and behave exactly the same (at least BoL says they do)I know I've been in a situation (SQL 2000) where the log and drive were so full that nothing worked, not truncate_only, not no_log.
well, well, SQL 2000 BOL says they are synonyms as well. I have been in situations where truncate_only did not work but no_log did, and I always thought no_log meant no marker was put in the log. This must have been version7, or even possibly 6.5 which would make more sense.
worth knowing I can't rely on this anymore though. This is a prime example of why I partake in this forum!
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply