February 10, 2010 at 6:26 pm
We have a custom application (VB6) using a SQL 2005 database. Recently, we noticed data suddenly disappearing. For example, a new customer account is created. This is a multi step process and several inserts are performed throughout the process. A clerk will perform a lookup after creating the account to verify the information is correct. The account is successfully found and reviewed during the lookup. However, later in the day, or the next day, the account cannot be found. There are no signs of the account ever being in any of the dozen or so tables. However, because we interface to an external payment system during the account creation process, we have a record of a transaction for the new account.
The users do not encounter any errors when creating new accounts. Some new accounts are fine, while others disappear at some later time. We can't seem to find a pattern to this issue. We have also had data disappear for other transactions, such as issuing an adjustment to an account, or updating account information.
Not sure what tools are available, or where I can start looking for the issue?
Do you have any suggestions?
February 10, 2010 at 7:35 pm
are there any triggers on the tables? it could be that you enter data into an application like your new customer account, and when it updates, the trigger fails and the data is not saved/lost; if the application does not check if the update was success/fail, then you might not know it is failing.
triggers are the first place i would look.
Lowell
February 10, 2010 at 10:39 pm
Check the following -
triggers
jobs that run stored procedures
VB code that "cleans up" data
Transactions that rollback.
Isolate when the data is changed.
To do this:
Run a trace while data entry is going on. If it is multiuser make sure you have more than one application open in order to recreate the fail state.
Add a trigger to copy data to an audit table.
Add logging to your VB code to dump when data is added, retrieved, deleted and updated.
Q Are you reusing a primary key when you add data?
My 2 cents worth.
Jamie Ingram
February 12, 2010 at 11:21 am
mwstewart did you find out what the problem was? are there any triggers, or did you find something else?
Lowell
February 12, 2010 at 11:30 am
Yes, there are triggers on certain tables. We check all of our new stored procedure that have been put into production since the problem started, but we can't find any problems. We do everything inside of a transaction and have error handling an rollbacks if there is a problem. The issue with data disappearing just isn't consistent. Sometimes we have a problem, but then we'll go days without a single incident. I'm not sure this could be a stored procedure issue.
We can't use a trigger to dump the data to an audit table, because if there are rollbacks occurring, the data in the audit table will be rolled back because we put everything inside of a transaction statement.
If we had to modify our VP app to do some logging, that would be a huge undertaking.
Just not sure where to go from here. The customer had HP take a look at their SAN and they say everything looks normal. Based on the time of day this has been occurring, I'm thinking it's a resource contention issue. Have you seen any problems with high usage and data disappearing because of low memory, cpu, or i/o availability on the server. How would we check for this?
Thanks in advance for the help.
February 12, 2010 at 12:25 pm
mwstewart (2/12/2010)
The customer had HP take a look at their SAN and they say everything looks normal. Based on the time of day this has been occurring, I'm thinking it's a resource contention issue. Have you seen any problems with high usage and data disappearing because of low memory, cpu, or i/o availability on the server. How would we check for this?.
SQL won't simply discard data no matter how much contention there is. Either there are transactions rolling back (possibly because of timeouts) or someone or something is deleting data from the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2010 at 12:52 pm
so you're saying that timeout issues will cause rollbacks? But shouldn't we see some evidence of this somewhere is some log? We have seen timeout issues on our app server. BTW, this is a three-tier application - VB client, App Server, SQL server. Shouldn't we be seeing rollbacks in the SQL logs?
The account creating process, where we're seeing data disappear, is modular. In other words, the user enters data on the first screen and that data is committed. The user is then taken to the second screen and that data is committed separately, and so on. So I wouldn't expect ALL data from the account creation to disappear, yet it is disappearing.
Also, regarding the timeouts. The user who enters/creates the new accounts has not been experiencing any system slow down, errors, or problems of any kind. He says the system is responding normally. Although other users have complained of their computer freezing in the middle of a transaction (and those have disappeared), the user inputing the new accounts hasn't had any problems.
This one really has us baffled and the customer is losing patience. Just not sure where to turn from here.
February 12, 2010 at 1:01 pm
mwstewart (2/12/2010)
so you're saying that timeout issues will cause rollbacks?
If the application timeout is exceeded, the application will tell SQL to stop processing that query (same as if you hit Stop in Management Studio). If there's an open transaction, that'll be rolled back.
But shouldn't we see some evidence of this somewhere is some log? We have seen timeout issues on our app server. BTW, this is a three-tier application - VB client, App Server, SQL server. Shouldn't we be seeing rollbacks in the SQL logs?
Rollbacks aren't sever enough errors to go into SQL's log. In fact, as far as SQL's concerned, they're not an error. They're just a case where the application said 'stop what you're doing'
If you're logging application errors, you'll be picking these up.
The account creating process, where we're seeing data disappear, is modular. In other words, the user enters data on the first screen and that data is committed. The user is then taken to the second screen and that data is committed separately, and so on. So I wouldn't expect ALL data from the account creation to disappear, yet it is disappearing.
Then look for someone or something that's deleting data. Profiler's good (or a server-side trace) as it will pick up everything that happens. It's a pain going through the logs, but if you need to track what's happening, that's the tool to use
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2010 at 4:12 pm
We're experiencing the 'disappearing data' issues this afternoon and we noticed numerous timeouts on our app server, where the middle-tier for the application is hosted. What could be causing these timeouts??
February 12, 2010 at 5:47 pm
We found a SPID that had a lock on a table, and others that were blocking other SPIDs. We had all the users exit the application and restart their workstations. When they fired up the app again, they were able to perform the activities that previously caused the timeouts with no errors this time. No sure why we were seeing the locks. Could this be the culprit?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply