October 12, 2010 at 9:09 am
Hi All,
In our one of the production databases, we are facing issue in one table.
every time we inserts some recods to that table and dont know how it happening, some one is deleting all the records from that table.
Is there any process to find out who is deleting those records.
Thank You.
Regards,
Raghavender Chavva
October 12, 2010 at 9:16 am
Do you have a trigger on the table?
October 18, 2010 at 10:10 pm
no, we don't have any triggers on that table
Thank You.
Regards,
Raghavender Chavva
October 18, 2010 at 10:12 pm
You could set up a server side trace on that database ?
October 18, 2010 at 10:16 pm
Yes, we did that but no use.
We have set up the Profiler, but no use.
Thank You.
Regards,
Raghavender Chavva
October 19, 2010 at 2:04 am
can you provide me the trigger which will collect the spid, loginname, and the host from where it is running, when a deletion occured.
Thank You.
Regards,
Raghavender Chavva
October 19, 2010 at 2:44 am
Tell us table definition and we can provide with the exact trigger ...!
Or if it is not allow to post you tab definition, you can try something like this:
CREATE THE TABLE WITH SAME STRUCTURE OF YOUR CURRENT TABLE, the easy way is - SELECT TOP 1 * FROM YOUR_TABLE INTO DEL_TABLE;
then modify your DEL_TABLE ADDING USERID & DATETIME_DELETED, MACHINE_NAME
ALTER TABLE DEL_TABLE
ADDing columns USERID, DateTime_Deleted, MachineName
Dont forget to TRUNCATE DEL_TABLE, to clear the data and preparing for the TRIGGER
then create the Trigger:
CREATE TRIGGER triger_name ON YOUR_TABLE
AFTER DELETE
AS
INSERT INTO (YOUR COLUMNS AT DEL_TABLE)
SELECT COLUMNS FROM DELETED
GO
Hope it helps, however tell us table definition ...!
:w00t:
October 19, 2010 at 4:03 am
Here is the table defination:
Coloumnname Type
idvarchar
part_numbervarchar
erial_novarchar
good_qtyint
bad_qtyint
locationnamevarchar
employeenovarchar
createdbyvarchar
creationdatedatetime
lastupdatedbyvarchar
lastupdatedatedatetime
lastupdateloginvarchar
downloadstatusflagvarchar
downloadbyvarchar
downloaddatedatetime
No Identity columns defined.
Thank You.
Regards,
Raghavender Chavva
October 19, 2010 at 5:31 am
Well you are going to create table where you will save the deleted records;
CREATE TABLE DELTAB
(
id VARCHAR(100),
part_number VARCHAR(100),
erial_no VARCHAR(20),
good_qty INT,
bad_qty INT,
locationname VARCHAR(20),
employeeno VARCHAR(20),
createdby VARCHAR(50),
creationdate DATETIME,
lastupdatedby VARCHAR(50),
lastupdatedate DATETIME,
lastupdatelogin VARCHAR(50),
downloadstatusflag VARCHAR(50),
downloadby VARCHAR(50),
downloaddate DATETIME,
USERID VARCHAR(200),
DATEDELETED DATETIME,
MACHINENAME VARCHAR(200)
)
GO
Then create the Trigger on your current table and after each deleted records you will have in DELTAB Table ...
CREATE TRIGGER DEL_INFO ON YOUR_TABLE
AFTER DELETE
AS
INSERT INTO [dbo].[DELTAB]
([id]
,[part_number]
,[erial_no]
,[good_qty]
,[bad_qty]
,[locationname]
,[employeeno]
,[createdby]
,[creationdate]
,[lastupdatedby]
,[lastupdatedate]
,[lastupdatelogin]
,[downloadstatusflag]
,[downloadby]
,[downloaddate]
,[USERID]
,[DATEDELETED]
,[MACHINENAME])
SELECT [id]
,[part_number]
,[erial_no]
,[good_qty]
,[bad_qty]
,[locationname]
,[employeeno]
,[createdby]
,[creationdate]
,[lastupdatedby]
,[lastupdatedate]
,[lastupdatelogin]
,[downloadstatusflag]
,[downloadby]
,[downloaddate]
,ORIGINAL_LOGIN()
,GETDATE()
,HOST_NAME()
FROM DELETED
October 19, 2010 at 3:38 pm
October 19, 2010 at 5:10 pm
pavan_srirangam (10/19/2010)
I think he needs find out who or what are doing the deletes not the data what is deleting.
Check out the last three columns in the new table... not only does it trap who/when/where, but also the data being deleted.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 19, 2010 at 5:30 pm
Raghavender (10/18/2010)
Yes, we did that but no use.We have set up the Profiler, but no use.
This doesn't make sense, if you put a filter on the table and select the right events, like SQL Statement start, you should get what you want.
If profiler isn't showing you the delete there are only a few possibilities:
1) Profiler isn't configured correctly - Very likely
2) The insert was never commited and is rolling back - not so likely
3) The table is being truncated - this would lose all data, so very unlikely
4) The database is being restored to an old version - Very, very unlikely
Using a trigger works, but it's an expensive way of doing this AND it involves changing the database schema, so it should go through a full test cycle. I would get the Profiler configuration sorted out, if you need to test options you select to make sure they work by configuring it on a test server first, and doing a delete to make sure it captures the delete.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply