Solved

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

Posted on 2008-06-14
11
382 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
  • 7
  • 3
11 Comments
 
LVL 142

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 300 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PK numbers not follow 8 49
create index to c1, c2 and c3 9 61
Set the max value for a column 7 34
Need help with a query 6 55
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now