Row are vanished from Table suddenly in SQL 2008

Question

Post reply

Row are vanished from Table suddenly in SQL 2008

pratiksawant28

SSC Rookie

Points: 32
More actions
July 21, 2014 at 5:31 am

#310135

Hi,
I have an database name ABC. In this DB i have table name XYZ.
This table has the rows more than 1000, but suddenly from yesterday is was showing only 250 rows.
Can you all please suggest on this, what all the possibilities in this?

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

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

gbritton1

SSCertifiable

Points: 6522

July 21, 2014 at 5:57 am

#1730968

another user deleted the rows

pratiksawant28 SSC Rookie Points: 32 More actions · Answer 2

Currently no other users are using this DB and admin access are with me only.

While checking have found that the one of the stored procedure was executed for deletion, i have got the last execution but can i found the executor for this procedure???

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

pratiksawant28 (7/21/2014)
Currently no other users are using this DB and admin access are with me only.
While checking have found that the one of the stored procedure was executed for deletion, i have got the last execution but can i found the executor for this procedure???

Unless that proc does it's own logging, you have a server side trace running, or some other feature (CDC???) running, then no. If someone had dropped the table, it would have shown up in the default trace if no one had turned it off.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 4

You might be able to get something from the log file depending on your recovery model.

Ultimately not a lot you can do with 2000 after the fact, if you haven't set up anything to capture or audit any sort of event.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

Some use triggers in these cases to potentially record changes to important information. These do add overhead to a tables performance but they exist for a reason.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

...and if the target table is designed properly and the trigger is written properly, they can be nasty fast.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

pratiksawant28 (7/21/2014)
Hi,
I have an database name ABC. In this DB i have table name XYZ.
This table has the rows more than 1000, but suddenly from yesterday is was showing only 250 rows.
Can you all please suggest on this, what all the possibilities in this?

Quick question, what version of SQL Server are you on (select @@version)?

😎