Disappearances of all (2000 more) records from one Table.

Question

Post reply

Disappearances of all (2000 more) records from one Table.

winbeing

Newbie

Points: 1
More actions
April 19, 2006 at 6:42 am

#167968

How to troubleshoot disappearances of all (2000 more) records from one
Table? Nothing shows up in TR LOG? Happens twice in a month.
Any other places to look? (MS SQL 2000 on win 2000 Server).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

could you please provide some more info ?!

1) does it happen at the same times/days each month ?! is there a pattern ?!

2) any triggers on the table ?! or any others that affect this one ?!

3) what is the ddl of this table ?!

**ASCII stupid question, get a stupid ANSI !!!**

Ryan Hathaway SSC Eights! Points: 827 More actions · Answer 2

Can you create triggers on TRUNCATE and DROP on this table to insert details on the caller into some new table?

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 3

I've seen this happen because a programmer had one "Begin Tran" too many.

When the user exited the program, all her changes were rolled back instead of commited.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 4

Maybe you have any job that is scheduled to 'clean' your table?

If it happens on a fixed time/date every month, schedule a trace for that time to see what happens.

And yes. You can create DDL triggers in SQL Server 2005. This of course implies that you have posted in the right forum because SQL Server 2000 does NOT support it.

Hanslindgren

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

Hi,

This is Minaz Amin,

As you have mentioned all the rows are disappeared from the table did any one fired truncate table command as this operation will not be logged in the T.log.

Truncate table will deallocate the pages. As this has happened twice is there any job schedule at truncate the tables. Check that?

Is the next disappearance of the rows happened at the same date/time of each month ?

Is there any stored procedure / query is executed from the application which will truncate the tables?

Running the profiler trace to capture the information will be a good option but you need to have hard disk space and need to check the trace output and if the deletion not happened then delete the trace file and wait for next trace file.

.

Do let me know further...

Thanks and Best of Regards,

Minaz Amin

Email : meenakshikundar@yahoo.com

Phno:9341735900

"More Green More Oxygen !! Plant a tree today"

bhaskar-299211 Newbie Points: 6 More actions · Answer 6

hi.

u can do the following to track the disappearance of records:

1. Create triggers on truncate, delete and drop events.

2. Run a trace file to capture the events.

3. Write a procedure that will update a flag whenever the count of the table gets decreased. Fire this procedure every 5 min by scheduling it in a job. Capture the results of sp_who2 command at the same time and insert them in some table to know the hostname and programname.