Data Loss?

Hi,
I'm sure this is a very broad question, but what could cause roughly
125,000 rows in one table to disappear over night. I'm running SQL server
7. I've done multiple virus scans...all clean...although mydoom was
previously found and quarantined.
Could someone give me at least a starting point on troubleshooting this.
The same thing happened to the same table about 3 months ago. At that time,
I figured it was some strange fluke, but now that it has happened again, it
is rather disconcerting.
Any help would be appreciated!!
Thanks,
Dan
Have you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
to see if somebody issued a DELETE statement?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>
|||Dan B wrote:
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL
> server 7. I've done multiple virus scans...all clean...although
> mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting
> this. The same thing happened to the same table about 3 months ago. At
> that time, I figured it was some strange fluke, but now that it
> has happened again, it is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
Make sure there are no running jobs in SQL Agent. Change all your
passwords on all logins that have delete access to the table or are in
the sysadmin group.
David Gugick
Imceda Software
www.imceda.com
|||"Dan B" <none@.none.com> wrote in message
news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm sure this is a very broad question, but what could cause roughly
> 125,000 rows in one table to disappear over night. I'm running SQL server
> 7. I've done multiple virus scans...all clean...although mydoom was
> previously found and quarantined.
> Could someone give me at least a starting point on troubleshooting this.
> The same thing happened to the same table about 3 months ago. At that
time,
> I figured it was some strange fluke, but now that it has happened again,
it
> is rather disconcerting.
> Any help would be appreciated!!
> Thanks,
> Dan
>
Perhaps you could add a trigger to the table that would write diagnostic
information to a logging table, such as time-of-deletion and possibly
information about the user that the deletion SQL was running as.
Regards,
Simon
|||I have a evaluation copy of Lumigent Log Explorer, which I used to the
extent the evaluation allows. I was able to when the deleting took place,
and the SPID of the user or computer it came from, but apparently SPIDs
change so whoever had that number on the day the deletions occurred has a
different SPID now. It looks like the 125,000 rows where deleted within
about 10 minutes. People who use the application that is tied to the
database cannot delete anything. I just can't see how this
happened....again.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Have you used a SQL log examination tool (Log P.I., Lumigent Log Explorer)
> to see if somebody issued a DELETE statement?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dan B" <none@.none.com> wrote in message
> news:u038qvI3EHA.2012@.TK2MSFTNGP15.phx.gbl...
> time,
> it
>
|||Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
Explorer)[vbcol=seagreen]
this.[vbcol=seagreen]
again,
>
|||You could turn on event auditing. Also, is the system secured? You don't
need your app to connect to the system and delete information. MS Access,
SS Query Analyzer, MS Query, etc., etc.
Is the application using Windows Authentication or SQL Server logins?
Logins per user or one application login? Is the password well known? What
is the application security level on the server and within the database? Is
the SA account secured? Who knows the password?
I'd be running audit traces pretty heavily until you found out who has been
connecting to the system directly.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uNkFfqJ3EHA.3416@.TK2MSFTNGP09.phx.gbl...
Lumigent Log Explorer will record logins and SPIDS so you can see extended
information about who and where the changes came from. If you know when it
is happening, you can run Profiler to grab complete information.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan B" <none@.none.com> wrote in message
news:%23O9sqnJ3EHA.3452@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I have a evaluation copy of Lumigent Log Explorer, which I used to the
> extent the evaluation allows. I was able to when the deleting took place,
> and the SPID of the user or computer it came from, but apparently SPIDs
> change so whoever had that number on the day the deletions occurred has a
> different SPID now. It looks like the 125,000 rows where deleted within
> about 10 minutes. People who use the application that is tied to the
> database cannot delete anything. I just can't see how this
> happened....again.
>
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OSKCb0I3EHA.3000@.TK2MSFTNGP15.phx.gbl...
Explorer)[vbcol=seagreen]
this.[vbcol=seagreen]
again,
>