Bizarre data loss running maintenance plan

I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
maintenance plan that backs up the database at 18:30 and backs up the
transaction log at midnight. Apparently the transaction log backups have
been failing because the database is set to the Simple recovery model. More
importantly, I'm losing updates to records that were updated prior to the
database backup! The inserted records are still there, but the updates
appear to have been rolled back (at least in one table where I've verifiied
the problem.) I'm running some more checks now and I've set the recovery
model to Full, but I'm interested in knowing if anyone else has seen anything
like this before.
hi,
Donald Welker wrote:
> I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
> maintenance plan that backs up the database at 18:30 and backs up the
> transaction log at midnight. Apparently the transaction log backups
> have been failing because the database is set to the Simple recovery
> model.
and this is a standard behavior :D

>More importantly, I'm losing updates to records that were
> updated prior to the database backup! The inserted records are still
> there, but the updates appear to have been rolled back (at least in
> one table where I've verifiied the problem.) I'm running some more
> checks now and I've set the recovery model to Full, but I'm
> interested in knowing if anyone else has seen anything like this
> before.
I do not think this has something to do with backup operation, even if
performed via Maintenance Plan (unfortunately a little buggy tool)... it's
actually not possible (and the simple recovery model is no guilty at all)
for a backup task to rollback update statements while committing insert...
the problem must be else where...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Overnight testing has isolated the occurence of the change to running the
transaction log backup at midnight. The data is fine at 23:55:01 and bad at
00:00:01. However it seems that I cannot reproduce this result even by
rescheduling the maintenance plan job steps to run in sequence during the
day. I've just dropped and replaced the maintenance plan (with a better
schedule) to see if that has any effect. Also, the new plan only backs up
this database rather than "All User Databases" (might also be related.)
"Andrea Montanari" wrote:

> hi,
> Donald Welker wrote:
> and this is a standard behavior :D
I've noticed.

> I do not think this has something to do with backup operation, even if
> performed via Maintenance Plan (unfortunately a little buggy tool)... it's
> actually not possible (and the simple recovery model is no guilty at all)
> for a backup task to rollback update statements while committing insert...
> the problem must be else where...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
It's happened three nights in a row so far, so it's certainly possible. It
still happened last night with Full recovery model, it just didn't record an
error anymore.