?
Solved

Why is data is disappearing from my SQL Server 2005 table?

Posted on 2008-06-14
11
Medium Priority
?
396 Views
Last Modified: 2012-08-13
I've been writing asp code for SQL Server web applications for years and have come up with a problem that has me stumped.

In short, data in one of my tables is periodically and arbitrarily being deleted.  Not ALL the data, just SOME of it.  And each time it happens, it is different data.  It's happening once or twice a week at inconsistent intervals as far as I can tell.

The table is quite narrow (only two fields) but also quite critical as it ties two other tables together and is really important for my client's cash flow.

I have checked the code inside and out and there are NO locations within the code from which to delete the data except one administrative module; using this module it would take DAYS (as well as admin clearance) to delete the amount of data that is regularly disappearing from this table, and those in the company with administrative clearance insist that they are not doing the deleting (there is no reason that they should!)

And yet the data continues to be deleted, and I continue having to restore an older versions of the table.

This system has over 80 tables and it's only happening to this one!  It's truly bizarre!

HOW THE HECK COULD THIS BE HAPPENING?  ALL IDEAS ARE WELCOME, THIS ONE HAS REALLY GOTTEN ME!

Thanks

Marc
0
Comment
Question by:marcgimbrere
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21787660
you methods (can be combined):
? create a trigger on the table, to log information when/who is doing the deletes.
? run a sql profiler to trace the deletes on that table

0
 

Author Comment

by:marcgimbrere
ID: 21787693
how do i set up a trigger on the table to log information when/who is doing the delete?
0
 
LVL 2

Accepted Solution

by:
vasureddym earned 1200 total points
ID: 21788041
create a log table and the trigger using the below code

once this code is executed, the audit (log) table and trigger are created. then , when ever, some one/thing deletes a record, the trigger inserts a record into the audit table along with the date deleted and who deteted.

NOTE: replace the string 'YOUR_TABLE' in the code with the your table name

Some useful links:
http://forums.devarticles.com/microsoft-sql-server-5/using-triggers-to-create-an-audit-record-3009.html
SELECT TOP 0 * 
INTO LOG_AUDIT_YOUR_TABLE
FROM YOUR_TABLE
GO
 
ALTER TABLE LOG_AUDIT_YOUR_TABLE ADD DeletedOn datetime, DeletedBy varchar(50)
GO
 
CREATE TRIGGER DBO.TRG_AUDIT_YOUR_TABLE
ON YOUR_TABLE
FOR DELETE
AS
--SET IDENTITY_INSERT LOG_AUDIT_YOUR_TABLE ON 
INSERT INTO LOG_AUDIT_YOUR_TABLE
SELECT *, GETDATE(), SYSTEM_USER
FROM deleted
--SET IDENTITY_INSERT LOG_AUDIT_YOUR_TABLE OFF 
GO
 
 
-- Note: use the two SET IDENTITY_INSERT commands when you see the below error
--'An explicit value for the identity column in table 'emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.'
-- (i.e. if your source table is having a identity key)

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:marcgimbrere
ID: 21790642
Thanks I am trying this approach top debug but of course it doesn't tell me what the core problem is...I'll get back to you when the mass deletion occurs again and we can look into the reasons why...meanwhile I'm leaving this open if thats OK with you.

Cheers

Marc
0
 

Author Comment

by:marcgimbrere
ID: 21791459
ok, so the question of the day is, can you set up a trigger to check to see if a single FIELD has changed?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 21791481
you have to check that in the trigger itself, see the UPDATE() function:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
<...>
Testing for UPDATE or INSERT Actions to Specific Columns

You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.
<...>
COLUMNS_UPDATED: http://msdn.microsoft.com/en-us/library/ms186329.aspx
UPDATE(): http://msdn.microsoft.com/en-us/library/ms187326.aspx

watch out, though, that when you have a column value of 'ABC', and the update sets the value to 'ABC' (the same value), both UPDATE() and COLUMNS_UPDATED will indicate that the column has been updated !
0
 

Author Closing Comment

by:marcgimbrere
ID: 31467304
Thanks, using these techniques I found
0
 

Author Comment

by:marcgimbrere
ID: 21791776
Using these techniques I found the error, thanks
0
 

Author Comment

by:marcgimbrere
ID: 21853920
OK, this is weird, I found the solution to the problem and deleted the trigger from the triggers section of the database, but now when I delete data from the table, the trigger still runs and throws an error because it cannot find the table to put the trigger information into:

Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name 'LOG_AUDIT_TBL_SPEAKER_KEYWORDS'.

/speaker_keywords.asp, line 19

I can't work out what is going on, there is nothing in the trigger section in SQL 2005 Management Studio - any ideas?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21853972
>I can't work out what is going on, there is nothing in the trigger section in SQL 2005 Management Studio - any ideas?

the triggers are listed below the table they are attached to.
what you see under "Server objects => Triggers" are the triggers for server-wide ddl triggers.
0
 

Author Comment

by:marcgimbrere
ID: 21854097
thanks, doh, I still have more to learn, that worked great
marc
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question