Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Random rows somehow leaving a table!!!
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ianwooly0
Starting Member

8 Posts

Posted - 01/15/2015 :  10:35:05  Show Profile  Reply with Quote
Good afternoon.

I have a web app that writes data to several different tables. When I create a data set through the app all the data writes to the relevant tables without any problems. However 1 table completely at random seomtimes loses rows of data. I can see the data used to be there as the row id`s no longer exist however I cannot pinpoint when and why this data is being removed from the table. There are over 335,000 rows in this table and just 79 rows that have magically disappeared!!! Any ideas?

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/15/2015 :  11:10:43  Show Profile  Reply with Quote
Check your transactions and triggers and whether someone else may be updating your table.
Go to Top of Page

ianwooly0
Starting Member

8 Posts

Posted - 01/16/2015 :  03:51:59  Show Profile  Reply with Quote
There is only one account that has access to the server with the database which uses windows authentication. And as I havent run any delete statements on this table I cant see another reason for the rows to go missing. Also, no triggers. Thank you though
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/16/2015 :  07:03:40  Show Profile  Reply with Quote
what sort of isolation level have you said in your transactions? I suspect it's a problem with transactions. It's as if some rows are not getting committed to the database.
Go to Top of Page

ianwooly0
Starting Member

8 Posts

Posted - 01/20/2015 :  06:32:30  Show Profile  Reply with Quote
The transaction isolation level is Read Committed. The table hints that the rows were created as the ID`s for the rows are missing which leads me to believe the data has been committed and then somehow removed.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/20/2015 :  07:01:03  Show Profile  Reply with Quote
please post your code that updates
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 01/21/2015 :  01:45:49  Show Profile  Visit jackv's Homepage  Reply with Quote
Is it only one type of action you're completing through the web app? Or is their multiple actions from the web app?
In other words, are you able to systematically go through one iteration and confirm the rows are available at the end .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4149 Posts

Posted - 01/21/2015 :  12:12:58  Show Profile  Visit graz's Homepage  Reply with Quote
How do you know the rows were ever there in the first place? Are you using IDENTITY for the Row IDs? If so, it is possible to generate an identity value, have the insert fail for some reason and you have an unused identity value. Also while identity normally doesn't skip numbers it doesn't guarantee that it doesn't skip numbers.



=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000