-1

I'm having some strange behavior with one data base on SQL Server 2005, i made an application to store some data, i have only 1 store procedure for custome querys in wich i only have some selects (no delete, or update operations) but for some reason, at a random moment (i havent found the exact moment when this happens) the data from my 2 important tables is someway deleted an the tables only keeps a couple of rows.

I use Mygeneration to map tha database on objects on .NET, and my methods only delete one row at time when is necesary,some times when i make some querys on SQL Server 2005 Managment studio the database don't finish the querys and it doesn't response.

I don't have any clue why this is happening and its giving me some headache at work. I'll apreciate some help with This. Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Augusto Cazares
  • 1
  • Are you the only one with access to this database? Are others able to run queries against it? Has it been recently restored from an old backup? – Oded Mar 09 '11 at 22:15
  • Well no one use the data base, only me, and yes i'm testing an application so i have restored the database a few times. – Augusto Cazares Mar 09 '11 at 22:18
  • So, it is entirely possible that you restored from a backup that doesn't have these rows. – Oded Mar 09 '11 at 22:19
  • That is not the case, because the rows get deleted in one instant to other, say i'm doing a query an everything is fine, and then i make another query and the data is lost. No restore between. – Augusto Cazares Mar 09 '11 at 22:22
  • Any triggers on any of your tables that perform deletes? – Martin Smith Mar 09 '11 at 22:29
  • @Augusto: "database don't finish the querys and it doesn't response" This sounds like blocking. Try running sp_who2 to see if there are blocked processes. Could be some transactions getting deadlocked and not committing the data you're trying to save. – tobias86 Mar 25 '11 at 07:19

1 Answers1

0

You need to find out who is doing the deletes. Create a FOR DELETE trigger which logs getutcdate(), SUSER_SNAME(), @@SPID and other interesting information to another table for each deletion.

However I suspect that your database is being completely deleted and recreated by MyGeneration, perhaps every time you rebuild all?

Ben
  • 34,935
  • 6
  • 74
  • 113