Backup issue: Transaction & Differential

I have a backup schedule to do transaction log every hour and it fails quite
often latetly. In order to have a continuous backup, I have to perform
differential in between which is not part of the backup plan. The error I
received when the transaction log failed is "the mdf file has been subjects
to nonlogged updates and cannot be rolled forward. Perform a full data or
differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
goal is to have transaction throughout the day not a mix bag of transaction
log and differential. Is there a way to resolve this issue or how it can be
prevented? Thanks!You can put the recovery mode in FULL instead of Bulk Logged and then
everything will be fully logged. You must have some sort of bulk operation
happening during the day which voids the point in time restore capability of
the log backups.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>I have a backup schedule to do transaction log every hour and it fails
>quite
> often latetly. In order to have a continuous backup, I have to perform
> differential in between which is not part of the backup plan. The error
> I
> received when the transaction log failed is "the mdf file has been
> subjects
> to nonlogged updates and cannot be rolled forward. Perform a full data or
> differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> goal is to have transaction throughout the day not a mix bag of
> transaction
> log and differential. Is there a way to resolve this issue or how it can
> be
> prevented? Thanks!|||What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better to
keep track of whether the db has been in simple mode and will provide a more useful error message if
such is the case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>I have a backup schedule to do transaction log every hour and it fails quite
> often latetly. In order to have a continuous backup, I have to perform
> differential in between which is not part of the backup plan. The error I
> received when the transaction log failed is "the mdf file has been subjects
> to nonlogged updates and cannot be rolled forward. Perform a full data or
> differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> goal is to have transaction throughout the day not a mix bag of transaction
> log and differential. Is there a way to resolve this issue or how it can be
> prevented? Thanks!|||It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
FULL. The transaction log ran correctly before last 3 weeks and we have BCP
processes running for last 2 years. I can see the BCP process but why it
occurred since last 2 weeks is a puzzle to me. I also issue a check point
oftenly, but it does not help to resolve the issue.
Due to a lot of different reasons, we are not able to upgrade to later
version of SQL Server yet, but planning is in progress. Is there a way to
fix this?
Thanks to both Andrew & Tibor.
"Tibor Karaszi" wrote:
> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better to
> keep track of whether the db has been in simple mode and will provide a more useful error message if
> such is the case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >I have a backup schedule to do transaction log every hour and it fails quite
> > often latetly. In order to have a continuous backup, I have to perform
> > differential in between which is not part of the backup plan. The error I
> > received when the transaction log failed is "the mdf file has been subjects
> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> > goal is to have transaction throughout the day not a mix bag of transaction
> > log and differential. Is there a way to resolve this issue or how it can be
> > prevented? Thanks!
>|||The recovery model concept was introduced in 2000. So you should check the settings for below
database options:
truncate log on checkpoint
select into/bulkcopy
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> processes running for last 2 years. I can see the BCP process but why it
> occurred since last 2 weeks is a puzzle to me. I also issue a check point
> oftenly, but it does not help to resolve the issue.
> Due to a lot of different reasons, we are not able to upgrade to later
> version of SQL Server yet, but planning is in progress. Is there a way to
> fix this?
> Thanks to both Andrew & Tibor.
>
> "Tibor Karaszi" wrote:
>> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
>> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better
>> to
>> keep track of whether the db has been in simple mode and will provide a more useful error message
>> if
>> such is the case.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >I have a backup schedule to do transaction log every hour and it fails quite
>> > often latetly. In order to have a continuous backup, I have to perform
>> > differential in between which is not part of the backup plan. The error I
>> > received when the transaction log failed is "the mdf file has been subjects
>> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> > goal is to have transaction throughout the day not a mix bag of transaction
>> > log and differential. Is there a way to resolve this issue or how it can be
>> > prevented? Thanks!
>>|||Three options are set to check for database options: select into/bulk copy,
auto update statistics, auto create statistics. Because I like to have
transaction running, therefore, I have truncate log on checkpoint turn off.
My understand this is the setting so that transaction log can occur. Is
there any other settings or patch I should take a look at? Thanks!
"Tibor Karaszi" wrote:
> The recovery model concept was introduced in 2000. So you should check the settings for below
> database options:
> truncate log on checkpoint
> select into/bulkcopy
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> > processes running for last 2 years. I can see the BCP process but why it
> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> > oftenly, but it does not help to resolve the issue.
> >
> > Due to a lot of different reasons, we are not able to upgrade to later
> > version of SQL Server yet, but planning is in progress. Is there a way to
> > fix this?
> >
> > Thanks to both Andrew & Tibor.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better
> >> to
> >> keep track of whether the db has been in simple mode and will provide a more useful error message
> >> if
> >> such is the case.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> > often latetly. In order to have a continuous backup, I have to perform
> >> > differential in between which is not part of the backup plan. The error I
> >> > received when the transaction log failed is "the mdf file has been subjects
> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> > prevented? Thanks!
> >>
> >>
>|||Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
operations. This is your problem.
7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does. In
2000, running in full recovery means that these potentially minimally logged operations will be
fully logged. But in earlier versions, some of these operations operation aren't allowed unless you
set "select into...". You can start by setting this to off and see if somebody screams. Or try to
track down the operation some other way and see what can be done about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> Three options are set to check for database options: select into/bulk copy,
> auto update statistics, auto create statistics. Because I like to have
> transaction running, therefore, I have truncate log on checkpoint turn off.
> My understand this is the setting so that transaction log can occur. Is
> there any other settings or patch I should take a look at? Thanks!
>
> "Tibor Karaszi" wrote:
>> The recovery model concept was introduced in 2000. So you should check the settings for below
>> database options:
>> truncate log on checkpoint
>> select into/bulkcopy
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> > processes running for last 2 years. I can see the BCP process but why it
>> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> > oftenly, but it does not help to resolve the issue.
>> >
>> > Due to a lot of different reasons, we are not able to upgrade to later
>> > version of SQL Server yet, but planning is in progress. Is there a way to
>> > fix this?
>> >
>> > Thanks to both Andrew & Tibor.
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
>> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
>> >> better
>> >> to
>> >> keep track of whether the db has been in simple mode and will provide a more useful error
>> >> message
>> >> if
>> >> such is the case.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> > differential in between which is not part of the backup plan. The error I
>> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> > prevented? Thanks!
>> >>
>> >>
>>|||Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
last 3 weeks even though we have queries with select into or BCP running. Is
there any configuration in version 7.0 that can be track or set? I do run
profile from time to time, but all processes that perform select into or BCP
are the same since last 2 years. That is the mystery and there is no
specific pattern that I can pinpoint so in order I can figure a work around.
I understand your explanation & concept, but I am still not clear why last 3
weeks but not any time before that.
"Tibor Karaszi" wrote:
> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
> operations. This is your problem.
> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does. In
> 2000, running in full recovery means that these potentially minimally logged operations will be
> fully logged. But in earlier versions, some of these operations operation aren't allowed unless you
> set "select into...". You can start by setting this to off and see if somebody screams. Or try to
> track down the operation some other way and see what can be done about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> > Three options are set to check for database options: select into/bulk copy,
> > auto update statistics, auto create statistics. Because I like to have
> > transaction running, therefore, I have truncate log on checkpoint turn off.
> > My understand this is the setting so that transaction log can occur. Is
> > there any other settings or patch I should take a look at? Thanks!
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The recovery model concept was introduced in 2000. So you should check the settings for below
> >> database options:
> >>
> >> truncate log on checkpoint
> >> select into/bulkcopy
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> >> > processes running for last 2 years. I can see the BCP process but why it
> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> >> > oftenly, but it does not help to resolve the issue.
> >> >
> >> > Due to a lot of different reasons, we are not able to upgrade to later
> >> > version of SQL Server yet, but planning is in progress. Is there a way to
> >> > fix this?
> >> >
> >> > Thanks to both Andrew & Tibor.
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
> >> >> better
> >> >> to
> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
> >> >> message
> >> >> if
> >> >> such is the case.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> >> > often latetly. In order to have a continuous backup, I have to perform
> >> >> > differential in between which is not part of the backup plan. The error I
> >> >> > received when the transaction log failed is "the mdf file has been subjects
> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> >> > prevented? Thanks!
> >> >>
> >> >>
> >>
> >>
>|||BCP does not constitute a Minimally logged load which is the issue here. It
is a Bulk Load but a Bulk load will still be fully logged unless you have
certain conditions set. I can't remember what this is under in BOL 7.0 but
it should be under BCP - xxx or Minimally logged load and will explain all
the conditions you need. So it is quite possible that you only recently did
something to meet all of the conditions.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> last 3 weeks even though we have queries with select into or BCP running.
> Is
> there any configuration in version 7.0 that can be track or set? I do run
> profile from time to time, but all processes that perform select into or
> BCP
> are the same since last 2 years. That is the mystery and there is no
> specific pattern that I can pinpoint so in order I can figure a work
> around.
> I understand your explanation & concept, but I am still not clear why last
> 3
> weeks but not any time before that.
> "Tibor Karaszi" wrote:
>> Since you have "select into/bulkcopy" turned on, you allow non-logged (or
>> minimally logged)
>> operations. This is your problem.
>> 7.0 and earlier versions didn't handle non-logged operations as neatly as
>> 2000 and later does. In
>> 2000, running in full recovery means that these potentially minimally
>> logged operations will be
>> fully logged. But in earlier versions, some of these operations operation
>> aren't allowed unless you
>> set "select into...". You can start by setting this to off and see if
>> somebody screams. Or try to
>> track down the operation some other way and see what can be done about
>> it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> > Three options are set to check for database options: select into/bulk
>> > copy,
>> > auto update statistics, auto create statistics. Because I like to
>> > have
>> > transaction running, therefore, I have truncate log on checkpoint turn
>> > off.
>> > My understand this is the setting so that transaction log can occur.
>> > Is
>> > there any other settings or patch I should take a look at? Thanks!
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> The recovery model concept was introduced in 2000. So you should check
>> >> the settings for below
>> >> database options:
>> >>
>> >> truncate log on checkpoint
>> >> select into/bulkcopy
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database
>> >> > is set to
>> >> > FULL. The transaction log ran correctly before last 3 weeks and we
>> >> > have BCP
>> >> > processes running for last 2 years. I can see the BCP process but
>> >> > why it
>> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check
>> >> > point
>> >> > oftenly, but it does not help to resolve the issue.
>> >> >
>> >> > Due to a lot of different reasons, we are not able to upgrade to
>> >> > later
>> >> > version of SQL Server yet, but planning is in progress. Is there a
>> >> > way to
>> >> > fix this?
>> >> >
>> >> > Thanks to both Andrew & Tibor.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server? I'd run a Profiler to catch what
>> >> >> non-logged operations has been
>> >> >> performed in your database. The reason I ask for version is that
>> >> >> AFAIK, SQL Server 2005 is
>> >> >> better
>> >> >> to
>> >> >> keep track of whether the db has been in simple mode and will
>> >> >> provide a more useful error
>> >> >> message
>> >> >> if
>> >> >> such is the case.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >I have a backup schedule to do transaction log every hour and it
>> >> >> >fails quite
>> >> >> > often latetly. In order to have a continuous backup, I have to
>> >> >> > perform
>> >> >> > differential in between which is not part of the backup plan.
>> >> >> > The error I
>> >> >> > received when the transaction log failed is "the mdf file has
>> >> >> > been subjects
>> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full
>> >> >> > data or
>> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE
>> >> >> > HY007]. My
>> >> >> > goal is to have transaction throughout the day not a mix bag of
>> >> >> > transaction
>> >> >> > log and differential. Is there a way to resolve this issue or
>> >> >> > how it can be
>> >> >> > prevented? Thanks!
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Thanks Andrew. I will look for more info regarding to BCP option that caused
this issue in 7.0 version.
"Andrew J. Kelly" wrote:
> BCP does not constitute a Minimally logged load which is the issue here. It
> is a Bulk Load but a Bulk load will still be fully logged unless you have
> certain conditions set. I can't remember what this is under in BOL 7.0 but
> it should be under BCP - xxx or Minimally logged load and will explain all
> the conditions you need. So it is quite possible that you only recently did
> something to meet all of the conditions.
> --
> Andrew J. Kelly SQL MVP
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> > Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> > last 3 weeks even though we have queries with select into or BCP running.
> > Is
> > there any configuration in version 7.0 that can be track or set? I do run
> > profile from time to time, but all processes that perform select into or
> > BCP
> > are the same since last 2 years. That is the mystery and there is no
> > specific pattern that I can pinpoint so in order I can figure a work
> > around.
> > I understand your explanation & concept, but I am still not clear why last
> > 3
> > weeks but not any time before that.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or
> >> minimally logged)
> >> operations. This is your problem.
> >>
> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as
> >> 2000 and later does. In
> >> 2000, running in full recovery means that these potentially minimally
> >> logged operations will be
> >> fully logged. But in earlier versions, some of these operations operation
> >> aren't allowed unless you
> >> set "select into...". You can start by setting this to off and see if
> >> somebody screams. Or try to
> >> track down the operation some other way and see what can be done about
> >> it.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> >> > Three options are set to check for database options: select into/bulk
> >> > copy,
> >> > auto update statistics, auto create statistics. Because I like to
> >> > have
> >> > transaction running, therefore, I have truncate log on checkpoint turn
> >> > off.
> >> > My understand this is the setting so that transaction log can occur.
> >> > Is
> >> > there any other settings or patch I should take a look at? Thanks!
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> The recovery model concept was introduced in 2000. So you should check
> >> >> the settings for below
> >> >> database options:
> >> >>
> >> >> truncate log on checkpoint
> >> >> select into/bulkcopy
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database
> >> >> > is set to
> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we
> >> >> > have BCP
> >> >> > processes running for last 2 years. I can see the BCP process but
> >> >> > why it
> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check
> >> >> > point
> >> >> > oftenly, but it does not help to resolve the issue.
> >> >> >
> >> >> > Due to a lot of different reasons, we are not able to upgrade to
> >> >> > later
> >> >> > version of SQL Server yet, but planning is in progress. Is there a
> >> >> > way to
> >> >> > fix this?
> >> >> >
> >> >> > Thanks to both Andrew & Tibor.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server? I'd run a Profiler to catch what
> >> >> >> non-logged operations has been
> >> >> >> performed in your database. The reason I ask for version is that
> >> >> >> AFAIK, SQL Server 2005 is
> >> >> >> better
> >> >> >> to
> >> >> >> keep track of whether the db has been in simple mode and will
> >> >> >> provide a more useful error
> >> >> >> message
> >> >> >> if
> >> >> >> such is the case.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >> >I have a backup schedule to do transaction log every hour and it
> >> >> >> >fails quite
> >> >> >> > often latetly. In order to have a continuous backup, I have to
> >> >> >> > perform
> >> >> >> > differential in between which is not part of the backup plan.
> >> >> >> > The error I
> >> >> >> > received when the transaction log failed is "the mdf file has
> >> >> >> > been subjects
> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full
> >> >> >> > data or
> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE
> >> >> >> > HY007]. My
> >> >> >> > goal is to have transaction throughout the day not a mix bag of
> >> >> >> > transaction
> >> >> >> > log and differential. Is there a way to resolve this issue or
> >> >> >> > how it can be
> >> >> >> > prevented? Thanks!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>|||I agree. I have a vague recollection that presence of indexes can affect whether a load will be
minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
> BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but a
> Bulk load will still be fully logged unless you have certain conditions set. I can't remember what
> this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
> explain all the conditions you need. So it is quite possible that you only recently did something
> to meet all of the conditions.
> --
> Andrew J. Kelly SQL MVP
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
>> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
>> last 3 weeks even though we have queries with select into or BCP running. Is
>> there any configuration in version 7.0 that can be track or set? I do run
>> profile from time to time, but all processes that perform select into or BCP
>> are the same since last 2 years. That is the mystery and there is no
>> specific pattern that I can pinpoint so in order I can figure a work around.
>> I understand your explanation & concept, but I am still not clear why last 3
>> weeks but not any time before that.
>> "Tibor Karaszi" wrote:
>> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
>> operations. This is your problem.
>> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does.
>> In
>> 2000, running in full recovery means that these potentially minimally logged operations will be
>> fully logged. But in earlier versions, some of these operations operation aren't allowed unless
>> you
>> set "select into...". You can start by setting this to off and see if somebody screams. Or try
>> to
>> track down the operation some other way and see what can be done about it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> > Three options are set to check for database options: select into/bulk copy,
>> > auto update statistics, auto create statistics. Because I like to have
>> > transaction running, therefore, I have truncate log on checkpoint turn off.
>> > My understand this is the setting so that transaction log can occur. Is
>> > there any other settings or patch I should take a look at? Thanks!
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> The recovery model concept was introduced in 2000. So you should check the settings for below
>> >> database options:
>> >>
>> >> truncate log on checkpoint
>> >> select into/bulkcopy
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> >> > processes running for last 2 years. I can see the BCP process but why it
>> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> >> > oftenly, but it does not help to resolve the issue.
>> >> >
>> >> > Due to a lot of different reasons, we are not able to upgrade to later
>> >> > version of SQL Server yet, but planning is in progress. Is there a way to
>> >> > fix this?
>> >> >
>> >> > Thanks to both Andrew & Tibor.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
>> >> >> been
>> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
>> >> >> better
>> >> >> to
>> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
>> >> >> message
>> >> >> if
>> >> >> such is the case.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> >> > differential in between which is not part of the backup plan. The error I
>> >> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> >> > prevented? Thanks!
>> >> >>
>> >> >>
>> >>
>> >>
>>
>|||The index has not change in the database for a while, at least 2 months. But
I do have a schedule of reindex, DBCC REINDEX, running nightly. Would this
cause this issue. Would select into statements cause the issue?
Thanks!
"Tibor Karaszi" wrote:
> I agree. I have a vague recollection that presence of indexes can affect whether a load will be
> minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
> > BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but a
> > Bulk load will still be fully logged unless you have certain conditions set. I can't remember what
> > this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
> > explain all the conditions you need. So it is quite possible that you only recently did something
> > to meet all of the conditions.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "KTN" <KTN@.discussions.microsoft.com> wrote in message
> > news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> >> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> >> last 3 weeks even though we have queries with select into or BCP running. Is
> >> there any configuration in version 7.0 that can be track or set? I do run
> >> profile from time to time, but all processes that perform select into or BCP
> >> are the same since last 2 years. That is the mystery and there is no
> >> specific pattern that I can pinpoint so in order I can figure a work around.
> >> I understand your explanation & concept, but I am still not clear why last 3
> >> weeks but not any time before that.
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
> >> operations. This is your problem.
> >>
> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does.
> >> In
> >> 2000, running in full recovery means that these potentially minimally logged operations will be
> >> fully logged. But in earlier versions, some of these operations operation aren't allowed unless
> >> you
> >> set "select into...". You can start by setting this to off and see if somebody screams. Or try
> >> to
> >> track down the operation some other way and see what can be done about it.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> >> > Three options are set to check for database options: select into/bulk copy,
> >> > auto update statistics, auto create statistics. Because I like to have
> >> > transaction running, therefore, I have truncate log on checkpoint turn off.
> >> > My understand this is the setting so that transaction log can occur. Is
> >> > there any other settings or patch I should take a look at? Thanks!
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> The recovery model concept was introduced in 2000. So you should check the settings for below
> >> >> database options:
> >> >>
> >> >> truncate log on checkpoint
> >> >> select into/bulkcopy
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> >> >> > processes running for last 2 years. I can see the BCP process but why it
> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> >> >> > oftenly, but it does not help to resolve the issue.
> >> >> >
> >> >> > Due to a lot of different reasons, we are not able to upgrade to later
> >> >> > version of SQL Server yet, but planning is in progress. Is there a way to
> >> >> > fix this?
> >> >> >
> >> >> > Thanks to both Andrew & Tibor.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
> >> >> >> been
> >> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
> >> >> >> better
> >> >> >> to
> >> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
> >> >> >> message
> >> >> >> if
> >> >> >> such is the case.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> >> >> > often latetly. In order to have a continuous backup, I have to perform
> >> >> >> > differential in between which is not part of the backup plan. The error I
> >> >> >> > received when the transaction log failed is "the mdf file has been subjects
> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> >> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> >> >> > prevented? Thanks!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||I don't recall whether DBCC DBREINDEX will break the log sequence chain in 7.0. It is possible.
SELECT INTO will, I know that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:2C657683-8090-4857-BCAD-9D4857CF32A8@.microsoft.com...
> The index has not change in the database for a while, at least 2 months. But
> I do have a schedule of reindex, DBCC REINDEX, running nightly. Would this
> cause this issue. Would select into statements cause the issue?
> Thanks!
> "Tibor Karaszi" wrote:
>> I agree. I have a vague recollection that presence of indexes can affect whether a load will be
>> minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
>> > BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but
>> > a
>> > Bulk load will still be fully logged unless you have certain conditions set. I can't remember
>> > what
>> > this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
>> > explain all the conditions you need. So it is quite possible that you only recently did
>> > something
>> > to meet all of the conditions.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> > "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> > news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
>> >> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
>> >> last 3 weeks even though we have queries with select into or BCP running. Is
>> >> there any configuration in version 7.0 that can be track or set? I do run
>> >> profile from time to time, but all processes that perform select into or BCP
>> >> are the same since last 2 years. That is the mystery and there is no
>> >> specific pattern that I can pinpoint so in order I can figure a work around.
>> >> I understand your explanation & concept, but I am still not clear why last 3
>> >> weeks but not any time before that.
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
>> >> operations. This is your problem.
>> >>
>> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later
>> >> does.
>> >> In
>> >> 2000, running in full recovery means that these potentially minimally logged operations will
>> >> be
>> >> fully logged. But in earlier versions, some of these operations operation aren't allowed
>> >> unless
>> >> you
>> >> set "select into...". You can start by setting this to off and see if somebody screams. Or
>> >> try
>> >> to
>> >> track down the operation some other way and see what can be done about it.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> >> > Three options are set to check for database options: select into/bulk copy,
>> >> > auto update statistics, auto create statistics. Because I like to have
>> >> > transaction running, therefore, I have truncate log on checkpoint turn off.
>> >> > My understand this is the setting so that transaction log can occur. Is
>> >> > there any other settings or patch I should take a look at? Thanks!
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> The recovery model concept was introduced in 2000. So you should check the settings for
>> >> >> below
>> >> >> database options:
>> >> >>
>> >> >> truncate log on checkpoint
>> >> >> select into/bulkcopy
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> >> >> > processes running for last 2 years. I can see the BCP process but why it
>> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> >> >> > oftenly, but it does not help to resolve the issue.
>> >> >> >
>> >> >> > Due to a lot of different reasons, we are not able to upgrade to later
>> >> >> > version of SQL Server yet, but planning is in progress. Is there a way to
>> >> >> > fix this?
>> >> >> >
>> >> >> > Thanks to both Andrew & Tibor.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Tibor Karaszi" wrote:
>> >> >> >
>> >> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
>> >> >> >> been
>> >> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005
>> >> >> >> is
>> >> >> >> better
>> >> >> >> to
>> >> >> >> keep track of whether the db has been in simple mode and will provide a more useful
>> >> >> >> error
>> >> >> >> message
>> >> >> >> if
>> >> >> >> such is the case.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://www.solidqualitylearning.com/
>> >> >> >>
>> >> >> >>
>> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> >> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> >> >> > differential in between which is not part of the backup plan. The error I
>> >> >> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> >> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> >> >> > prevented? Thanks!
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>