May 27, 2008 at 2:08 am
We have an SQL 2005 database in which all our data is stored (clients, prospects, orders, deliveries, invoices, aso...)
The client-table contains then basis data (client number is an 8 char string), The 'prospect-table' contains project-related data such as dates, link to contacts, lead origin , project issue ... Besides the 8 char string for the client number, it has a 3 char string 'filenumber'.
Until there everything ok. Then we have a bunch of tables related to that last table with quotes, orders, invoices, deliveries, contacts, ... and last but not least a table containing action-logs for that client in that project. In that table (containing 28000 rows more or less) specific rows disappear everytime, even when I put them back, they go away after a few days. And it's always for the same clients, the same projects, the records disappear. Nothing disappears in other tables, nothing in new 'clients-prospects' ...
No errors, no deletion logs, no replication errors, just gone...
Anyone seen a mystery like this? (be sure I tried almost everything, giving this 'old' client a 'new' number seems to be the only solution, but then we have a lot of work)
May 27, 2008 at 2:34 am
No truncate (which is only minimal logged)?
Any open transactions rolled back?
N 56°04'39.16"
E 12°55'05.25"
May 27, 2008 at 3:42 am
I'd try one of the log reader tools available, to read through your logs, you may be able to download the tool for a short trial, which might be enough time to find when the records where removed.
May 27, 2008 at 5:39 am
No, no truncate, nothing logged, put the records back from an old backup, they disappear. Make a new record with that typical client-number/project number: the record is gone after a few days.
May 27, 2008 at 5:44 am
Dear Carolyn,
Thanks, I'll try that, though I already know when they disappear, it has nothing to do with users synchronizing (I thought so at first), nothing to do with database maintenance. I let a sp run every ten minutes, giving me feedback on how many records (of these typical clients) disappear. It stays stable, until we add records, related to the clients already in the "black list" of disappearing records, they stay for a while and then disappear, without any reason... others don't.
What is the tool you are speaking about?
Regards
Johan
May 27, 2008 at 6:20 am
If it is a specific client number, I would add a trigger that errors on delete of that client number and wait for an application to error - or if you cannot have an error, you could use the trigger to log to another table the host for the process, the time, the user, the application, etc.
May 27, 2008 at 6:42 am
Have you run Profiler to watch the code come across? You can at least see what procedure calls are being made with what parameters and then test those same calls yourself. That would be my first step. Second step would be run Profiler to capture individual statements within the procedures. Only do this on a limited basis once you've identified the source of the problem though because the statement captures produce reams of data and put a bit of a load on the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2008 at 9:25 am
Things to look for: foreign keys with ON DELETE CASCADE, and Insert/Update triggers that are executing Deletes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 9:47 am
It's amazing the response I'm getting! Thank you all very much. However, many of the things I already tried. I'll have to run the profiler and check the procedures passing by, and try to trigger it myself like Grant suggested. The problem is, that it's very time consuming...
Something to do in the holidays...
Thank you all for the suggestions, I'll give you feedback soon!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply