Discussion:
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.
(too old to reply)
Doctor Who
2008-01-11 15:04:05 UTC
Permalink
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
log file:
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Kevin3NF
2008-01-11 15:09:16 UTC
Permalink
If the full only takes "a second" to run, just run it hourly in Simple
Recovery if you want near-time recovery and no worries about the t-log. Set
your retention appropriately
--
Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Tibor Karaszi
2008-01-11 16:09:03 UTC
Permalink
What you describe is not a very common scenario (i.e. running in full but not do log backups, and
reason for running in full is so you can backup log when db goes suspect). However, you can
accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
model and then back to full recovery model again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
TheSQLGuru
2008-01-11 18:18:21 UTC
Permalink
Post by Tibor Karaszi
What you describe is not a very common scenario (i.e. running in full but
not do log backups
Actually I have to take exception to that one Tibor. This forum and others
have numerous examples of users who complain "my data file is NNN MB and my
tlog is MM GB, what is going on!?!?". Default settings lead many users who
aren't DBAs (99.438% of them) to have FULL recovery mode doing FULL (or
often even no) backups without doing tlog backups. I guess you probably
consult at larger companies that have more significant problems than things
like this. :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
Post by Tibor Karaszi
What you describe is not a very common scenario (i.e. running in full but
not do log backups, and reason for running in full is so you can backup
log when db goes suspect). However, you can accomplish the same thing as
BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple recovery
model and then back to full recovery model again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Tibor Karaszi
2008-01-11 20:06:55 UTC
Permalink
Actually I have to take exception to that one Tibor. This forum and others have numerous examples
of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
Oh, I now see that I should have qualified my statement. It isn't a very common scenario *when the
dba understand recovery models and transaction logging*. I.e., to do this deliberately.

I definitely agree that it is unfortunately common to have this setup undeliberately. I've always
thought that full recovery model as default is a good thing. But lately, I've been having some
doubts. Perhaps SQL Server should default recovery model for the model database to simple. Would
reduce a lot of the cases we see in this newsgroup, for instance.
I guess you probably consult at larger companies that have more significant problems than things
like this. :-))
Nah, I do both. And I see this in the most surprising situations... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Post by Tibor Karaszi
What you describe is not a very common scenario (i.e. running in full but not do log backups
Actually I have to take exception to that one Tibor. This forum and others have numerous examples
of users who complain "my data file is NNN MB and my tlog is MM GB, what is going on!?!?".
Default settings lead many users who aren't DBAs (99.438% of them) to have FULL recovery mode
doing FULL (or often even no) backups without doing tlog backups. I guess you probably consult at
larger companies that have more significant problems than things like this. :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
Post by Tibor Karaszi
What you describe is not a very common scenario (i.e. running in full but not do log backups, and
reason for running in full is so you can backup log when db goes suspect). However, you can
accomplish the same thing as BACKUP LOG ... WITH TRUNCATE ONLY by setting the db in simple
recovery model and then back to full recovery model again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Chris Wood
2008-01-14 16:07:22 UTC
Permalink
One reason you see the message that you are is that it is being removed in
SQL2008. See http://www.mssqltips.com/tip.asp?tip=1352 and
http://www.mssqltips.com/tip.asp?tip=1370

Chris
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Doctor Who
2008-01-14 22:17:42 UTC
Permalink
I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods. If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Greg D. Moore (Strider)
2008-01-15 01:45:36 UTC
Permalink
Post by Doctor Who
I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods.
How do you figure? If you truncate the log you can't later recover it.

Do you mean the other way around?
Post by Doctor Who
If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Tibor Karaszi
2008-01-15 06:57:51 UTC
Permalink
Post by Doctor Who
I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only
That doesn't give you anything compared to simple recovery model. What you later have in the log
isn't usable for recovery purposes since you truncated the log directly after your database backup.
It would be better to have simple recovery model.

If you do it the other way around (BACKUP LOG WITH TRUNCATE ONLY immediately before the database
backup), then you have a possible advantage compared to simple recovery model. If the database
becomes suspect, you can do a log backup. But this is a pretty extreme case, and I would suggest
that you do regular log backups instead.
Post by Doctor Who
If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
As I replied earlier, it is only the command which will be removed. The *functionality* is still
there. Put the db in simple recovery then immediately to full again. This gives you the same effect
as BACKUP LOG WITH TRUNCATE ONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Post by Doctor Who
I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods. If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Chris Wood
2008-01-15 16:12:35 UTC
Permalink
You can always make the log backup be deleted after one hour, the shortest
time currently allowed.

Chris
Post by Doctor Who
I think from the replies, my point has been lost, which is that doing a full
back up followed immediately by a log backup using truncate only, is better
than using a simple model because it protects against data loss between
backup time periods. If this feature is going to be removed in 2008 than a
dba with a situtation like mine will have to either choose between allowing a
certain amount of data loss (simple) or using (full) and creating tran log
backups that he really doesn't want and has to clean up.
Post by Doctor Who
I have choosen the Full model over the simple model in a development
environment because if the database was to go suspect using the simple model,
I would have to choose how much work I was willing to lo lose. For example if
the database was backed up at 10:00am and the next backup was due at 4:00pm
if it went suspect at 3:00pm, I would lose the work from 10:00 to 3:00pm.
While using the Full model I should be able to recover up to 3:00pm. Now
because this is a very small database, only takes a second to do a Full
backup, it really doesn't pay to keep backups of the log, but in order to
keep the log file from growing and growing, I clean it out so that it will
shrink by backing up with troncate_only. I get the following message in the
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.
Now I have to say in the situtation that I have described, isn't using a
Full model better than a simple?
Loading...