Post reply

Data Loss during Maint Plan Optimization Job

  • February 15, 2006 at 1:10 am

    #116156

    Here's an issue I've been dealing with over the last couple of days. A DB re-index and space removal job was scheduled to run around the same time as some externally scheduled application jobs hit the same DB. After running for several months with no problems there were two incidents of data loss.

    By rolling through the transaction logs in standby mode I was able to determine the 5 minute window in a 1.5 GB TLOG where the table row count dropped to zero. However I've used Lumigent and RedGate log explorer tools to try and see what occurred to the table but both tools have reported no activity on the table within the log!

    Originally I thought the app jobs ran into re-index table locks and malfunctioned causing the delete. I would of expected to see delete or truncate SQL get picked up by the log explorer tools. The creation date of the table has not changed so it's not like it's been dropped and re-created.

    Anyone got any ideas?

    Edit: Specs SQL 2000 SP4 on Windows Ent 2003

  • February 15, 2006 at 7:13 am

    #621021

    Possibly during a clustered index delection the row count was not available, but I've never heard of any data being lost during the optmization jobs.

  • February 15, 2006 at 4:22 pm

    #621227

    Thanks for the response. The data has been removed from the table. I'd be very suprised if the re-index and or space removal operation was responsible.

    I guess I'm looking for reasons why the activity that removed the data would be hidden from log analysis tools. I've been in contact with RedGate about the issue but they've come up with nothing so far.

  • February 15, 2006 at 4:28 pm

    #621228

    did the row count was zero or there was no data in the table when u queried it?...

    -Krishnan

  • February 15, 2006 at 5:00 pm

    #621235

    All the data in the table was removed.

    Note the table has a single primary key which is also a clustered index.

  • February 15, 2006 at 6:13 pm

    #621244

    I haven't seen data being lost due to maintenance jobs..I would be interested to know if it has..

    If the table wasn't dropped and recreated then the third party tool should have logged the delete or truncate..

    Also was the recovery model changed to bulk logged during the optimization job?

     

    -Krishnan

  • February 15, 2006 at 7:52 pm

    #621253

    No change to the recovery model were made during the optimization job.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply