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?
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.
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>
|||> 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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
> 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
>
> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>
|||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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>
|||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.
"Doctor Who" wrote:
> 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?
>
|||"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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?
[vbcol=seagreen]
> 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.
> "Doctor Who" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You can always make the log backup be deleted after one hour, the shortest
time currently allowed.
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...[vbcol=seagreen]
>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.
> "Doctor Who" wrote:
Showing posts with label deprecated. Show all posts
Showing posts with label deprecated. Show all posts
Sunday, February 19, 2012
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.
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?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.
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||> 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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
> 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
>
> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>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?
>|||> 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
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13ofcn7dvasj99c@.corp.supernews.com...
>> 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
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
>> 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
>>
>> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
>> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>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?
>>
>|||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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||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.
"Doctor Who" wrote:
> 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?
>|||"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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?
> 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.
> "Doctor Who" wrote:
>> 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?
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>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.
> 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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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.
> "Doctor Who" wrote:
>> 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?|||You can always make the log backup be deleted after one hour, the shortest
time currently allowed.
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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.
> "Doctor Who" wrote:
>> 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?
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?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.
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||> 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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
> 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
>
> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>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?
>|||> 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
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13ofcn7dvasj99c@.corp.supernews.com...
>> 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
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:B057ED66-437A-4D44-8F31-DECF8EE07867@.microsoft.com...
>> 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
>>
>> "Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
>> news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>>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?
>>
>|||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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:A187F9FA-1580-40D7-9BCD-78D5C5839293@.microsoft.com...
>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?
>|||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.
"Doctor Who" wrote:
> 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?
>|||"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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?
> 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.
> "Doctor Who" wrote:
>> 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?
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>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.
> 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
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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.
> "Doctor Who" wrote:
>> 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?|||You can always make the log backup be deleted after one hour, the shortest
time currently allowed.
Chris
"Doctor Who" <DoctorWho@.discussions.microsoft.com> wrote in message
news:23C1E85F-C5F3-4265-83B2-696AE487EDA3@.microsoft.com...
>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.
> "Doctor Who" wrote:
>> 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?
BACKUP LOG WITH TRUNCATE_ONLY is deprecated in 2005
In my backup program (originally for 2000), for databases not in Full, or
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
JayI think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
Ekrem nsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>|||Jay,
I think you will achieve the same functionality by scheduling the CHECKPOINT
command in tempdb. This
shouldn't be needed, but if you encounter a case where tempdb log seems to b
e growing even though it
shouldn't be it is something to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...[vbc
ol=seagreen]
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb W
ITH TRUNCATE_ONLY' in
>his script.
> In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode
> database's log file after a checkpoint and that checkpoint is only guarant
eed to fire when the log
> is 70% full, otherwise it won't fire until the system reaches the (rather
fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the complet
ed transactions in a
> simple mode transaction log file, which will minimize the chance that the
file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the
> truncation of Simple mode transaction log files, so the original purpose o
f backing up a Simple
> mode log with truncate_only would still apply. Yet Microsoft has deprecate
d the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL Serv
er log every time you
> use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNG
P05.phx.gbl...
>[/vbcol]|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||> So, it would be completly OK to remove the code that backs up the transaction logs in data
bases
> that are set to Simple recovery mode.
Probably. I'm vague, because we know that all programs has bugs in them, and
I don't want to rule
out the possibility that there might exist some bugs regarding the automatic
truncation of log
handling...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...[vbcol
=seagreen]
> So, it would be completly OK to remove the code that backs up the transact
ion logs in databases
> that are set to Simple recovery mode.
> I need to also find out how often the servers are doing checkpoints too, b
ut I'll find it.
> Thanks,
> jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]|||
> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncat
ion. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every t
ime a checkpoint occurs.
Google can probably help you find it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WEZIAxIIHA.3940@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
>I looked up the CHECKPOINT in 2000 BOL and the scope of the command is vagu
e. While some of the
>references imply its scope is database specific (specifcally the thing abou
t recovery time), since
>it is dealing with the buffer cache (a resources shared by all databases in
an instance), it makes
>more sense that the scope of a checkpoint would be global.
> Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm
> guessing the logs, but havn't been able to check them yet.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
JayI think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
Ekrem nsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>|||Jay,
I think you will achieve the same functionality by scheduling the CHECKPOINT
command in tempdb. This
shouldn't be needed, but if you encounter a case where tempdb log seems to b
e growing even though it
shouldn't be it is something to consider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...[vbc
ol=seagreen]
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb W
ITH TRUNCATE_ONLY' in
>his script.
> In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode
> database's log file after a checkpoint and that checkpoint is only guarant
eed to fire when the log
> is 70% full, otherwise it won't fire until the system reaches the (rather
fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the complet
ed transactions in a
> simple mode transaction log file, which will minimize the chance that the
file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the
> truncation of Simple mode transaction log files, so the original purpose o
f backing up a Simple
> mode log with truncate_only would still apply. Yet Microsoft has deprecate
d the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL Serv
er log every time you
> use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNG
P05.phx.gbl...
>[/vbcol]|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||> So, it would be completly OK to remove the code that backs up the transaction logs in data
bases
> that are set to Simple recovery mode.
Probably. I'm vague, because we know that all programs has bugs in them, and
I don't want to rule
out the possibility that there might exist some bugs regarding the automatic
truncation of log
handling...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...[vbcol
=seagreen]
> So, it would be completly OK to remove the code that backs up the transact
ion logs in databases
> that are set to Simple recovery mode.
> I need to also find out how often the servers are doing checkpoints too, b
ut I'll find it.
> Thanks,
> jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]|||
> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncat
ion. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every t
ime a checkpoint occurs.
Google can probably help you find it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WEZIAxIIHA.3940@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
>I looked up the CHECKPOINT in 2000 BOL and the scope of the command is vagu
e. While some of the
>references imply its scope is database specific (specifcally the thing abou
t recovery time), since
>it is dealing with the buffer cache (a resources shared by all databases in
an instance), it makes
>more sense that the scope of a checkpoint would be global.
> Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm
> guessing the logs, but havn't been able to check them yet.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>[/vbcol]
BACKUP LOG WITH TRUNCATE_ONLY is deprecated in 2005
In my backup program (originally for 2000), for databases not in Full, or
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
Jay
I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>
|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
Ekrem nsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>
|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>
|||
> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
Jay
I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>
|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
Ekrem nsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>
|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>
|||
> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>
BACKUP LOG WITH TRUNCATE_ONLY is deprecated in 2005
In my backup program (originally for 2000), for databases not in Full, or
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
JayI think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
--
Ekrem Önsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or
>> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there for
>> backing up 2000, or 2005 databases.
>> Jay
>|||Jay,
I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb. This
shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even though it
shouldn't be it is something to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>his script.
> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the log
> is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and the
> truncation of Simple mode transaction log files, so the original purpose of backing up a Simple
> mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL Server log every time you
> use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to backup
>> the log for a database in Simple recovery mode - I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>>WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70% full,
>> otherwise it won't fire until the system reaches the (rather fuzzy)
>> recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which will
>> minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction log
>> files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages
>> to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there
>> for backing up 2000, or 2005 databases.
>> Jay
>>
>|||> So, it would be completly OK to remove the code that backs up the transaction logs in databases
> that are set to Simple recovery mode.
Probably. I'm vague, because we know that all programs has bugs in them, and I don't want to rule
out the possibility that there might exist some bugs regarding the automatic truncation of log
handling...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
> So, it would be completly OK to remove the code that backs up the transaction logs in databases
> that are set to Simple recovery mode.
> I need to also find out how often the servers are doing checkpoints too, but I'll find it.
> Thanks,
> jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb.
>> This shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even
>> though it shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>>his script.
>> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
>> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the
>> log is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
>> interval.
>> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
>> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and
>> the truncation of Simple mode transaction log files, so the original purpose of backing up a
>> Simple mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages to the SQL Server log
>> every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to
>> backup the log for a database in Simple recovery mode - I supose I've learned a little since
>> then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>|||> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>> So, it would be completly OK to remove the code that backs up the
>> transaction logs in databases that are set to Simple recovery mode.
>> I need to also find out how often the servers are doing checkpoints too,
>> but I'll find it.
>> Thanks,
>> jay
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the
>> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
>> encounter a case where tempdb log seems to be growing even though it
>> shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG
>>tempdb WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70%
>> full, otherwise it won't fire until the system reaches the (rather
>> fuzzy) recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which
>> will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction
>> log files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write
>> messages to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_
>> WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought
>> to question need to backup the log for a database in Simple recovery
>> mode - I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY' in the backup script (only for Simple mode DB's) should
>> even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>>
>|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>>WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70% full,
>> otherwise it won't fire until the system reaches the (rather fuzzy)
>> recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which will
>> minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction log
>> files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages
>> to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there
>> for backing up 2000, or 2005 databases.
>> Jay
>>
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncation. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every time a checkpoint occurs.
Google can probably help you find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WEZIAxIIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I looked up the CHECKPOINT in 2000 BOL and the scope of the command is vague. While some of the
>references imply its scope is database specific (specifcally the thing about recovery time), since
>it is dealing with the buffer cache (a resources shared by all databases in an instance), it makes
>more sense that the scope of a checkpoint would be global.
> Also, I'm still looking for something to tell me when the checkpoints are actually happening. I'm
> guessing the logs, but havn't been able to check them yet.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb.
>> This shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even
>> though it shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>>his script.
>> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
>> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the
>> log is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
>> interval.
>> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
>> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and
>> the truncation of Simple mode transaction log files, so the original purpose of backing up a
>> Simple mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages to the SQL Server log
>> every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to
>> backup the log for a database in Simple recovery mode - I supose I've learned a little since
>> then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>
Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
TRUNCATE_ONLY'.
Now I originally copied this from RealSQLGuy's site and never thought to
question need to backup the log for a database in Simple recovery mode - I
supose I've learned a little since then.
So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in
the backup script (only for Simple mode DB's) should even be there for
backing up 2000, or 2005 databases.
JayI think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
WITH TRUNCATE_ONLY' in his script.
In SQL Server 2000, the system will only remove the completed transactions
from a Simple mode database's log file after a checkpoint and that
checkpoint is only guaranteed to fire when the log is 70% full, otherwise it
won't fire until the system reaches the (rather fuzzy) recovery interval.
So, the goal looks like it was to guarantee a regular flush of the completed
transactions in a simple mode transaction log file, which will minimize the
chance that the file will auto-grow.
What I don't understand is that 2005 seems to function the same in reguard
to checkpoints and the truncation of Simple mode transaction log files, so
the original purpose of backing up a Simple mode log with truncate_only
would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
option to the backup log statement and they write messages to the SQL Server
log every time you use it.
Confused.
"Jay" <nospam@.nospam.org> wrote in message
news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
> In my backup program (originally for 2000), for databases not in Full, or
> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
> TRUNCATE_ONLY'.
> Now I originally copied this from RealSQLGuy's site and never thought to
> question need to backup the log for a database in Simple recovery mode - I
> supose I've learned a little since then.
> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
> in the backup script (only for Simple mode DB's) should even be there for
> backing up 2000, or 2005 databases.
> Jay
>|||TRUNCATE_ONLY would break the log chain that's why it'll be deprecated.
Also, checkpoint does not come into action only when log's 70% is full.
There are also other reasons for checkpoint. They are listed in BOL. You can
take a look at the following link to learn more about these triggers.
http://msdn2.microsoft.com/en-us/library/ms189573.aspx
--
Ekrem Önsoy
"Jay" <nospam@.nospam.org> wrote in message
news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>WITH TRUNCATE_ONLY' in his script.
> In SQL Server 2000, the system will only remove the completed transactions
> from a Simple mode database's log file after a checkpoint and that
> checkpoint is only guaranteed to fire when the log is 70% full, otherwise
> it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the
> completed transactions in a simple mode transaction log file, which will
> minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard
> to checkpoints and the truncation of Simple mode transaction log files, so
> the original purpose of backing up a Simple mode log with truncate_only
> would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL
> Server log every time you use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message
> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or
>> Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there for
>> backing up 2000, or 2005 databases.
>> Jay
>|||Jay,
I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb. This
shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even though it
shouldn't be it is something to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>his script.
> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the log
> is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
> interval.
> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and the
> truncation of Simple mode transaction log files, so the original purpose of backing up a Simple
> mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH TRUNCATE_ONLY
> option to the backup log statement and they write messages to the SQL Server log every time you
> use it.
> Confused.
> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to backup
>> the log for a database in Simple recovery mode - I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>|||So, it would be completly OK to remove the code that backs up the
transaction logs in databases that are set to Simple recovery mode.
I need to also find out how often the servers are doing checkpoints too, but
I'll find it.
Thanks,
jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>>WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70% full,
>> otherwise it won't fire until the system reaches the (rather fuzzy)
>> recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which will
>> minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction log
>> files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages
>> to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there
>> for backing up 2000, or 2005 databases.
>> Jay
>>
>|||> So, it would be completly OK to remove the code that backs up the transaction logs in databases
> that are set to Simple recovery mode.
Probably. I'm vague, because we know that all programs has bugs in them, and I don't want to rule
out the possibility that there might exist some bugs regarding the automatic truncation of log
handling...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
> So, it would be completly OK to remove the code that backs up the transaction logs in databases
> that are set to Simple recovery mode.
> I need to also find out how often the servers are doing checkpoints too, but I'll find it.
> Thanks,
> jay
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb.
>> This shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even
>> though it shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>>his script.
>> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
>> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the
>> log is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
>> interval.
>> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
>> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and
>> the truncation of Simple mode transaction log files, so the original purpose of backing up a
>> Simple mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages to the SQL Server log
>> every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to
>> backup the log for a database in Simple recovery mode - I supose I've learned a little since
>> then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>|||> Probably. I'm vague, because we know that all programs has bugs in them,
> and I don't want to rule
> out the possibility that there might exist some bugs regarding the
> automatic truncation of log handling...
Do you think that is why RealSQLGuy put the TRUNCATE in his code? I supose I
should fire him an email as he seems to have inactive for about 6 months
now.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OyzkgQuIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>> So, it would be completly OK to remove the code that backs up the
>> transaction logs in databases that are set to Simple recovery mode.
>> I need to also find out how often the servers are doing checkpoints too,
>> but I'll find it.
>> Thanks,
>> jay
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the
>> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
>> encounter a case where tempdb log seems to be growing even though it
>> shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG
>>tempdb WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70%
>> full, otherwise it won't fire until the system reaches the (rather
>> fuzzy) recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which
>> will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction
>> log files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write
>> messages to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_
>> WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought
>> to question need to backup the log for a database in Simple recovery
>> mode - I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY' in the backup script (only for Simple mode DB's) should
>> even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>>
>|||I looked up the CHECKPOINT in 2000 BOL and the scope of the command is
vague. While some of the references imply its scope is database specific
(specifcally the thing about recovery time), since it is dealing with the
buffer cache (a resources shared by all databases in an instance), it makes
more sense that the scope of a checkpoint would be global.
Also, I'm still looking for something to tell me when the checkpoints are
actually happening. I'm guessing the logs, but havn't been able to check
them yet.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
> Jay,
> I think you will achieve the same functionality by scheduling the
> CHECKPOINT command in tempdb. This shouldn't be needed, but if you
> encounter a case where tempdb log seems to be growing even though it
> shouldn't be it is something to consider.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb
>>WITH TRUNCATE_ONLY' in his script.
>> In SQL Server 2000, the system will only remove the completed
>> transactions from a Simple mode database's log file after a checkpoint
>> and that checkpoint is only guaranteed to fire when the log is 70% full,
>> otherwise it won't fire until the system reaches the (rather fuzzy)
>> recovery interval.
>> So, the goal looks like it was to guarantee a regular flush of the
>> completed transactions in a simple mode transaction log file, which will
>> minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in
>> reguard to checkpoints and the truncation of Simple mode transaction log
>> files, so the original purpose of backing up a Simple mode log with
>> truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages
>> to the SQL Server log every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full,
>> or Bulk Logged mode (ie: Simple), I'm doing a 'BACLUP LOG _dbname_ WITH
>> TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to
>> question need to backup the log for a database in Simple recovery mode -
>> I supose I've learned a little since then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'
>> in the backup script (only for Simple mode DB's) should even be there
>> for backing up 2000, or 2005 databases.
>> Jay
>>
>|||AFAIK, the CHECKPOINT command is database specific regarding the log truncation. I believe that
there's a trace flag you can turn on to get an entry in the errorlog every time a checkpoint occurs.
Google can probably help you find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:%23WEZIAxIIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I looked up the CHECKPOINT in 2000 BOL and the scope of the command is vague. While some of the
>references imply its scope is database specific (specifcally the thing about recovery time), since
>it is dealing with the buffer cache (a resources shared by all databases in an instance), it makes
>more sense that the scope of a checkpoint would be global.
> Also, I'm still looking for something to tell me when the checkpoints are actually happening. I'm
> guessing the logs, but havn't been able to check them yet.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:EFCFA152-82C5-4171-A3EA-1B7339D71FF1@.microsoft.com...
>> Jay,
>> I think you will achieve the same functionality by scheduling the CHECKPOINT command in tempdb.
>> This shouldn't be needed, but if you encounter a case where tempdb log seems to be growing even
>> though it shouldn't be it is something to consider.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:ehlhvJlIIHA.5904@.TK2MSFTNGP04.phx.gbl...
>>I think I may have found the reason RealSQLGuy put the 'BACKUP LOG tempdb WITH TRUNCATE_ONLY' in
>>his script.
>> In SQL Server 2000, the system will only remove the completed transactions from a Simple mode
>> database's log file after a checkpoint and that checkpoint is only guaranteed to fire when the
>> log is 70% full, otherwise it won't fire until the system reaches the (rather fuzzy) recovery
>> interval.
>> So, the goal looks like it was to guarantee a regular flush of the completed transactions in a
>> simple mode transaction log file, which will minimize the chance that the file will auto-grow.
>> What I don't understand is that 2005 seems to function the same in reguard to checkpoints and
>> the truncation of Simple mode transaction log files, so the original purpose of backing up a
>> Simple mode log with truncate_only would still apply. Yet Microsoft has deprecated the WITH
>> TRUNCATE_ONLY option to the backup log statement and they write messages to the SQL Server log
>> every time you use it.
>> Confused.
>> "Jay" <nospam@.nospam.org> wrote in message news:u4RMKZkIIHA.6068@.TK2MSFTNGP05.phx.gbl...
>> In my backup program (originally for 2000), for databases not in Full, or Bulk Logged mode (ie:
>> Simple), I'm doing a 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY'.
>> Now I originally copied this from RealSQLGuy's site and never thought to question need to
>> backup the log for a database in Simple recovery mode - I supose I've learned a little since
>> then.
>> So, I'm wondering if the lines 'BACLUP LOG _dbname_ WITH TRUNCATE_ONLY' in the backup script
>> (only for Simple mode DB's) should even be there for backing up 2000, or 2005 databases.
>> Jay
>>
>>
>
Subscribe to:
Posts (Atom)