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 no_log. Show all posts
Showing posts with label no_log. 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 NO_LOG - change recovery model to SIMPLE?
Hi,
I use FULL recovery model, SQL 2005. Is it possible this type of
backup ""change"" my recovery model to SIMPLE. I noticed when I
executed this:
BACKUP DATABSE db_name
TO DISK = 'path'
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
Now transact log grow very, very, very slow (this is symptom simple
model). But when executed this (different order):
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
BACKUP DATABSE db_name
TO DISK = 'path'
transact log grow normally
Would somebody explain me this, and tell me first statement change
(theoretically) my model to SIMPLE?
--
RegardsHi
First , read this article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
<anxcomp@.gmail.com> wrote in message
news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||This is expected. If you are in full mode and empty the log without actually doing a backup (which
is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
Server know this and in this situation, the database acts as if it is simple recovery (log is
auto-truncated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||On Aug 21, 2:52 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <anxc...@.gmail.com> wrote in messagenews:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> > Hi,
> > I use FULL recovery model, SQL 2005. Is it possible this type of
> > backup ""change"" my recovery model to SIMPLE. I noticed when I
> > executed this:
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > Now transact log grow very, very, very slow (this is symptom simple
> > model). But when executed this (different order):
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > transact log grow normally
> > Would somebody explain me this, and tell me first statement change
> > (theoretically) my model to SIMPLE?
> > --
> > Regards- Hide quoted text -
> - Show quoted text -
to change recovery model from full to simple use alter database
command for that database
alter database dbname set recovery=simple
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
setting to simple recovery helps to minimize log file growth ,but note
you will not be able to do point in time recovery incase of failures
Thanks
VS|||On 21 Sie, 11:52, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
So it is realy true, it change to SIMPLE mode, thanks.
Tibor I read your article ' Why you want to be restrictive with
shrink of database files', I understand I shouldn't use both
SHRINKFILE and SHRINKDATABASE command.
So, would you help me create good backup plan, which ENSURE me that I
shouldn't USE SHRINK* commands, please.
Main principles:
1.LOG can't grow so match (most important)
2 I can lost information max fifteen minutes back
3. I'd like use only full recovery model not SIMPLE
If you like it can be for example graphical plan - "Maintenance Plans"
on SQL 2005
Thank you
--
Regards|||> So it is realy true, it change to SIMPLE mode, thanks.
No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
same as in simple model. Important distinction.
> 1.LOG can't grow so match (most important)
OK. You handle this by doing frequent log backups since a lot backup will empty the ldf file(s).
> 2 I can lost information max fifteen minutes back
So you should do log backups at least in 15 minutes intervals. Perhaps every 10 minutes...
> 3. I'd like use only full recovery model not SIMPLE
So just keep the database in full recovery.
Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
minutes. This is easy thing to setup with the maint wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187727440.811711.299920@.a39g2000hsc.googlegroups.com...
> On 21 Sie, 11:52, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> This is expected. If you are in full mode and empty the log without actually doing a backup
>> (which
>> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
>> Server know this and in this situation, the database acts as if it is simple recovery (log is
>> auto-truncated).
> So it is realy true, it change to SIMPLE mode, thanks.
> Tibor I read your article ' Why you want to be restrictive with
> shrink of database files', I understand I shouldn't use both
> SHRINKFILE and SHRINKDATABASE command.
> So, would you help me create good backup plan, which ENSURE me that I
> shouldn't USE SHRINK* commands, please.
> Main principles:
> 1.LOG can't grow so match (most important)
> 2 I can lost information max fifteen minutes back
> 3. I'd like use only full recovery model not SIMPLE
> If you like it can be for example graphical plan - "Maintenance Plans"
> on SQL 2005
> Thank you
> --
> Regards
>|||> No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
> same as in simple model. Important distinction.
OK, I understand now.
> Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
> minutes. This is easy thing to setup with the maint wizard.
I've done this use Wizard and now I'm watching what happen with log :)
Thank you Tibor for all advices and other people too :)
--
Regards
I use FULL recovery model, SQL 2005. Is it possible this type of
backup ""change"" my recovery model to SIMPLE. I noticed when I
executed this:
BACKUP DATABSE db_name
TO DISK = 'path'
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
Now transact log grow very, very, very slow (this is symptom simple
model). But when executed this (different order):
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
BACKUP DATABSE db_name
TO DISK = 'path'
transact log grow normally
Would somebody explain me this, and tell me first statement change
(theoretically) my model to SIMPLE?
--
RegardsHi
First , read this article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
<anxcomp@.gmail.com> wrote in message
news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||This is expected. If you are in full mode and empty the log without actually doing a backup (which
is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
Server know this and in this situation, the database acts as if it is simple recovery (log is
auto-truncated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||On Aug 21, 2:52 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <anxc...@.gmail.com> wrote in messagenews:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> > Hi,
> > I use FULL recovery model, SQL 2005. Is it possible this type of
> > backup ""change"" my recovery model to SIMPLE. I noticed when I
> > executed this:
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > Now transact log grow very, very, very slow (this is symptom simple
> > model). But when executed this (different order):
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > transact log grow normally
> > Would somebody explain me this, and tell me first statement change
> > (theoretically) my model to SIMPLE?
> > --
> > Regards- Hide quoted text -
> - Show quoted text -
to change recovery model from full to simple use alter database
command for that database
alter database dbname set recovery=simple
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
setting to simple recovery helps to minimize log file growth ,but note
you will not be able to do point in time recovery incase of failures
Thanks
VS|||On 21 Sie, 11:52, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
So it is realy true, it change to SIMPLE mode, thanks.
Tibor I read your article ' Why you want to be restrictive with
shrink of database files', I understand I shouldn't use both
SHRINKFILE and SHRINKDATABASE command.
So, would you help me create good backup plan, which ENSURE me that I
shouldn't USE SHRINK* commands, please.
Main principles:
1.LOG can't grow so match (most important)
2 I can lost information max fifteen minutes back
3. I'd like use only full recovery model not SIMPLE
If you like it can be for example graphical plan - "Maintenance Plans"
on SQL 2005
Thank you
--
Regards|||> So it is realy true, it change to SIMPLE mode, thanks.
No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
same as in simple model. Important distinction.
> 1.LOG can't grow so match (most important)
OK. You handle this by doing frequent log backups since a lot backup will empty the ldf file(s).
> 2 I can lost information max fifteen minutes back
So you should do log backups at least in 15 minutes intervals. Perhaps every 10 minutes...
> 3. I'd like use only full recovery model not SIMPLE
So just keep the database in full recovery.
Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
minutes. This is easy thing to setup with the maint wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187727440.811711.299920@.a39g2000hsc.googlegroups.com...
> On 21 Sie, 11:52, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> This is expected. If you are in full mode and empty the log without actually doing a backup
>> (which
>> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
>> Server know this and in this situation, the database acts as if it is simple recovery (log is
>> auto-truncated).
> So it is realy true, it change to SIMPLE mode, thanks.
> Tibor I read your article ' Why you want to be restrictive with
> shrink of database files', I understand I shouldn't use both
> SHRINKFILE and SHRINKDATABASE command.
> So, would you help me create good backup plan, which ENSURE me that I
> shouldn't USE SHRINK* commands, please.
> Main principles:
> 1.LOG can't grow so match (most important)
> 2 I can lost information max fifteen minutes back
> 3. I'd like use only full recovery model not SIMPLE
> If you like it can be for example graphical plan - "Maintenance Plans"
> on SQL 2005
> Thank you
> --
> Regards
>|||> No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
> same as in simple model. Important distinction.
OK, I understand now.
> Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
> minutes. This is easy thing to setup with the maint wizard.
I've done this use Wizard and now I'm watching what happen with log :)
Thank you Tibor for all advices and other people too :)
--
Regards
Backup log with no_log
Hi,
Every day we perform a full database backup.
(during the night).
Since we need no "additional" backups we had the problem that the
transaction log file grows and grows.
Therefore now just after the backup we perform a backup of the transaction
log with no log.
So we free up the space in the log file. (it truncates).
However, now we get this errormessage:
PRB: A "Database log truncated" Error is Logged in the Event Log When You
Try to Back Up the Transaction Log
http://support.microsoft.com/defaul...ben-us%3b818202
I am aware of this message but I think that it is basically a warning.
Am I correct ?
Arno de Jong, The Netherlands.The message is a warning that your database is using the Full recovery
model, but you have truncated the log and so it will not be possible to
recover to a point in time, or following a system failure - you will have to
load your last full backup, and any changes after that are lost.
If I understand your description correctly, you do a full backup every
night, but no transaction log backups during the day. If so, you might
consider putting the database in the Simple recovery model, which means the
log will not grow so large, and you will not see these warning messages. The
disadvantage is that if the system fails, you will lose all modifications
since the last full backup, but it looks like you don't want or need fuller
recovery anyway.
The BOL topic "Selecting a Recovery Model" has a good explanation of the
differences between the various recovery models.
Simon
"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bdidgh$cn7$1@.reader08.wxs.nl...
> Hi,
> Every day we perform a full database backup.
> (during the night).
> Since we need no "additional" backups we had the problem that the
> transaction log file grows and grows.
> Therefore now just after the backup we perform a backup of the transaction
> log with no log.
> So we free up the space in the log file. (it truncates).
> However, now we get this errormessage:
> PRB: A "Database log truncated" Error is Logged in the Event Log When You
> Try to Back Up the Transaction Log
> http://support.microsoft.com/defaul...ben-us%3b818202
>
>
> I am aware of this message but I think that it is basically a warning.
> Am I correct ?
> Arno de Jong, The Netherlands.|||Hi Simon,
So it means that it is not possible to restore my database to any time
after the last backup is taken ?
But the transactions after the log file is shrunken are still in the
transaction log file, aren't they ??
So I would think that I still can restore up to any time ?
Suppose I want still be able to restore up to any time (since last full
backup) AND I would like to shrink my transaction log file: what is the
alternative ?
Bye
Arno de Jong
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See comments inline.
"Arno De Jong" <anonymous@.devdex.com> wrote in message
news:3efecadc$0$198$75868355@.news.frii.net...
> Hi Simon,
> So it means that it is not possible to restore my database to any time
> after the last backup is taken ?
Right, because NO_LOG means all transactions after the full backup took
place are lost.
> But the transactions after the log file is shrunken are still in the
> transaction log file, aren't they ??
No - if you execute backup log ... with no_log then you lose transactions
completely.
> So I would think that I still can restore up to any time ?
No - point in time recovery is only possible if you have backed up the
transaction log, and you have not truncated it with NO_LOG since the last
full (or perhaps differential) backup. Check out "How to restore to the
point of failure (Transact-SQL)", and "Recovering to a Point In Time" in
Books Online.
> Suppose I want still be able to restore up to any time (since last full
> backup) AND I would like to shrink my transaction log file: what is the
> alternative ?
Then you should consider using Full (or possibly Bulk-Logged) recovery
model, with regular transaction log backups during the day - look at the
topic "Transaction Log Backups" in BOL for an example.
BACKUP LOG always frees up space inside the log file, even without NO_LOG,
but doesn't make the log files any smaller physically. The space will get
re-used for logging future transactions, or you can physically shrink the
log files to reclaim the space. "Shrinking the Transaction Log" in BOL gives
a detailed explanation of logical vs physical space in the logs.
> Bye
> Arno de Jong
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Thursday, February 16, 2012
BACKUP LOG DB WITH NO_LOG
our transaction log filled and I ran:
BACKUP LOG DB WITH NO_LOG
returns this error message:
Time out occurred while waiting for buffer latch type 3
for page (1:25176642), database ID 11, object ID
1517456680, index ID 0.
Does anyone have a clue on his (SQL Server 7)?Your disk subsystem is probably overloaded and is taking too long to do the
necessary read and writes. I would stop user access if you can until you
can fix the problem.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:96f101c3ea6a$f5ea93d0$a401280a@.phx.gbl...
your SQL error logs. This error that you are running isnt so much a
'Trasnaction Log' issue, but more of a Disk I/O issue.A latch is a physical
lock that lets you acquire access on the page you need ot perform your
operation on. Due to some disk i/o access, you got this error whichmeans
that the command was unable to obtain a latch.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
BACKUP LOG DB WITH NO_LOG
returns this error message:
Time out occurred while waiting for buffer latch type 3
for page (1:25176642), database ID 11, object ID
1517456680, index ID 0.
Does anyone have a clue on his (SQL Server 7)?Your disk subsystem is probably overloaded and is taking too long to do the
necessary read and writes. I would stop user access if you can until you
can fix the problem.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:96f101c3ea6a$f5ea93d0$a401280a@.phx.gbl...
quote:|||I take it that this occured only once? you';ll see more error messages in
> our transaction log filled and I ran:
> BACKUP LOG DB WITH NO_LOG
> returns this error message:
> Time out occurred while waiting for buffer latch type 3
> for page (1:25176642), database ID 11, object ID
> 1517456680, index ID 0.
> Does anyone have a clue on his (SQL Server 7)?
your SQL error logs. This error that you are running isnt so much a
'Trasnaction Log' issue, but more of a Disk I/O issue.A latch is a physical
lock that lets you acquire access on the page you need ot perform your
operation on. Due to some disk i/o access, you got this error whichmeans
that the command was unable to obtain a latch.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
BACKUP LOG DB WITH NO_LOG
our transaction log filled and I ran:
BACKUP LOG DB WITH NO_LOG
returns this error message:
Time out occurred while waiting for buffer latch type 3
for page (1:25176642), database ID 11, object ID
1517456680, index ID 0.
Does anyone have a clue on his (SQL Server 7)?Your disk subsystem is probably overloaded and is taking too long to do the
necessary read and writes. I would stop user access if you can until you
can fix the problem.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:96f101c3ea6a$f5ea93d0$a401280a@.phx.gbl...
> our transaction log filled and I ran:
> BACKUP LOG DB WITH NO_LOG
> returns this error message:
> Time out occurred while waiting for buffer latch type 3
> for page (1:25176642), database ID 11, object ID
> 1517456680, index ID 0.
> Does anyone have a clue on his (SQL Server 7)?|||I take it that this occured only once? you';ll see more error messages in
your SQL error logs. This error that you are running isnt so much a
'Trasnaction Log' issue, but more of a Disk I/O issue.A latch is a physical
lock that lets you acquire access on the page you need ot perform your
operation on. Due to some disk i/o access, you got this error whichmeans
that the command was unable to obtain a latch.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
BACKUP LOG DB WITH NO_LOG
returns this error message:
Time out occurred while waiting for buffer latch type 3
for page (1:25176642), database ID 11, object ID
1517456680, index ID 0.
Does anyone have a clue on his (SQL Server 7)?Your disk subsystem is probably overloaded and is taking too long to do the
necessary read and writes. I would stop user access if you can until you
can fix the problem.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:96f101c3ea6a$f5ea93d0$a401280a@.phx.gbl...
> our transaction log filled and I ran:
> BACKUP LOG DB WITH NO_LOG
> returns this error message:
> Time out occurred while waiting for buffer latch type 3
> for page (1:25176642), database ID 11, object ID
> 1517456680, index ID 0.
> Does anyone have a clue on his (SQL Server 7)?|||I take it that this occured only once? you';ll see more error messages in
your SQL error logs. This error that you are running isnt so much a
'Trasnaction Log' issue, but more of a Disk I/O issue.A latch is a physical
lock that lets you acquire access on the page you need ot perform your
operation on. Due to some disk i/o access, you got this error whichmeans
that the command was unable to obtain a latch.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Subscribe to:
Posts (Atom)