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 HY
007]. 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 b
e
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 oper
ations 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 quit
e
> 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 subject
s
> 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 transactio
n
> 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 op
erations has been
> performed in your database. The reason I ask for version is that AFAIK, SQ
L Server 2005 is better to
> keep track of whether the db has been in simple mode and will provide a mo
re 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...
>|||The recovery model concept was introduced in 2000. So you should check the s
ettings 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...[vbcol=seagreen]
> 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 B
CP
> 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:
>|||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...
>|||Since you have "select into/bulkcopy" turned on, you allow non-logged (or mi
nimally logged)
operations. This is your problem.
7.0 and earlier versions didn't handle non-logged operations as neatly as 20
00 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 ar
en't allowed unless you
set "select into...". You can start by setting this to off and see if somebo
dy 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...[vbcol=seagreen]
> Three options are set to check for database options: select into/bulk co
py,
> 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:
>|||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. I
s
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 logg
ed 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 some
body 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...
>|||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...[vbcol=seagreen]
> 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:
>|||Thanks Andrew. I will look for more info regarding to BCP option that cause
d
this issue in 7.0 version.
"Andrew J. Kelly" wrote:

> BCP does not constitute a Minimally logged load which is the issue here. I
t
> 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 di
d
> 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...
>
>