We have a strange problem with an SQL SERVER 2000 database, it doesn't
seem like a corruption problem but I can't identify what's happening.
I'd appreciate any ideas.
On Wednesday we noticed that 85 records in a person table had been
added. These appear to be old versions of existing records. Most fields
are duplicates, a few are slightly different, some are null. For
example, Joe Smith now has 2 records instead of one, with different
phone numbers. The primary key is an autoincrement field and the new
records have normally incremented ID's. The user interface is a c# web
app using stored procedures. The 'last_update_date' field is blank on
the duplicate records and this is added by the stored procedure using
GetDate() so it doesn't appear that the application code or the SP
created the bad records.
On Thursday, a similar problem occurred on a different table but on a
larger scale - a 10,000 record table increased to 30,000, basically 3
copies of each record. This table also uses an autoincrement ID, this
time there is a gap of a couple of thousand berfore the new records
begin.
DBCC CheckDB finds no problems. A standard maintenance script (rebuild
indexes etc. ran without errors on the previous Sunday night. Nightly
full backups for the previous days are OK. Inside a firewall, only IT
folks have direct access, virus software running.
In the meantime, I've added some unique constraints on last/first names
in the person table to try to catch an error if it happens again. but
we are running out of ideas, it seems impossible but... any
suggestions appreciated.I would run a profiler over night and see if you can see anything unsual.
"GregC" wrote:
> We have a strange problem with an SQL SERVER 2000 database, it doesn't
> seem like a corruption problem but I can't identify what's happening.
> I'd appreciate any ideas.
> On Wednesday we noticed that 85 records in a person table had been
> added. These appear to be old versions of existing records. Most fields
> are duplicates, a few are slightly different, some are null. For
> example, Joe Smith now has 2 records instead of one, with different
> phone numbers. The primary key is an autoincrement field and the new
> records have normally incremented ID's. The user interface is a c# web
> app using stored procedures. The 'last_update_date' field is blank on
> the duplicate records and this is added by the stored procedure using
> GetDate() so it doesn't appear that the application code or the SP
> created the bad records.
> On Thursday, a similar problem occurred on a different table but on a
> larger scale - a 10,000 record table increased to 30,000, basically 3
> copies of each record. This table also uses an autoincrement ID, this
> time there is a gap of a couple of thousand berfore the new records
> begin.
> DBCC CheckDB finds no problems. A standard maintenance script (rebuild
> indexes etc. ran without errors on the previous Sunday night. Nightly
> full backups for the previous days are OK. Inside a firewall, only IT
> folks have direct access, virus software running.
> In the meantime, I've added some unique constraints on last/first names
> in the person table to try to catch an error if it happens again. but
> we are running out of ideas, it seems impossible but... any
> suggestions appreciated.
>|||Also, I would enable login auditing so you can see who is logging into the
instance. Then, I would grab a copy of Lumigent's Log Explorer and open up
the tranaction log to find the transactions that put the data into the
table. Many times they will be tagged with a network name or some other
form of a value to ID the workstation the command came from.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"SBAUER" <SBAUER@.discussions.microsoft.com> wrote in message
news:AAECCB2C-1143-4943-AF43-037BF273AF55@.microsoft.com...
>I would run a profiler over night and see if you can see anything unsual.
>
> "GregC" wrote:
>> We have a strange problem with an SQL SERVER 2000 database, it doesn't
>> seem like a corruption problem but I can't identify what's happening.
>> I'd appreciate any ideas.
>> On Wednesday we noticed that 85 records in a person table had been
>> added. These appear to be old versions of existing records. Most fields
>> are duplicates, a few are slightly different, some are null. For
>> example, Joe Smith now has 2 records instead of one, with different
>> phone numbers. The primary key is an autoincrement field and the new
>> records have normally incremented ID's. The user interface is a c# web
>> app using stored procedures. The 'last_update_date' field is blank on
>> the duplicate records and this is added by the stored procedure using
>> GetDate() so it doesn't appear that the application code or the SP
>> created the bad records.
>> On Thursday, a similar problem occurred on a different table but on a
>> larger scale - a 10,000 record table increased to 30,000, basically 3
>> copies of each record. This table also uses an autoincrement ID, this
>> time there is a gap of a couple of thousand berfore the new records
>> begin.
>> DBCC CheckDB finds no problems. A standard maintenance script (rebuild
>> indexes etc. ran without errors on the previous Sunday night. Nightly
>> full backups for the previous days are OK. Inside a firewall, only IT
>> folks have direct access, virus software running.
>> In the meantime, I've added some unique constraints on last/first names
>> in the person table to try to catch an error if it happens again. but
>> we are running out of ideas, it seems impossible but... any
>> suggestions appreciated.
>>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment