Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue.

New records are inserted using a stored procedure, which will return an error code to the application if something goes wrong.

The Application, SQL Server, System Logs do not show any errors during the time when these 2 transactions should have been inserted.

How do I go about identifying the issue here? My current idea is to find a way to read the Transaction Log (from a backup restore) to see if the transactions are even in the log and if they got rolled back.

Can anyone recommend anything else I can do to troubleshoot this? Are there DMVs or other (SQL Server hidden) logs?

I am worrying about this issue and what SQL Server did in this situation. (I have experienced issues recently with timeouts, but since a recent reboot, this problem was resolved....)

share|improve this question
    
Similar issue I have found: dbforums.com/microsoft-sql-server/… - I wonder how to find this Rollback transaction to get the data that was lost. –  Dina May 14 '13 at 19:47
    
Highly unlikely that SQL Server will loose any transaction. If something goes wrong, its upto your application to log it or catch it. It will be a good idea to talk to your business or stake holders to work with end users to get the 2 records that you missed instead of going through the pain of reading Transaction logs. You can find a way to read them using - undocumented --fn_dblog and fn_dump_dblog described here sqlskills.com/blogs/paul/… –  Kin May 14 '13 at 22:02
    
@Kin Yes! That is the plan regarding finding missing transactions but I want to read the log simply to confirm what transpired. (If it was a rollback or they are entirely missed) I do not want a repeat of this occurrence. –  Dina May 15 '13 at 14:21
    
The bug mentioned in that forum post is with the ExecuteNonquery which in this case is a Java component not a SQL Server component. –  mrdenny May 15 '13 at 14:57
    
@mrdenny Agreed. Just a DBA/Sysadmin here and do not know application errors specifically. I know that the application has not changed for a very long time & it's a new issue. –  Dina May 15 '13 at 15:21
show 3 more comments

2 Answers

You can use ApexSQL Log to read the transaction log (online, backup, detached). Even in the trial, it shows in GUI all transactions it finds in logs. That can help you troubleshoot the issue.

share|improve this answer
1  
You can also use Quest LiteSpeed or Quest Toad for SQL Server, both of which read transaction logs (online, backup, or detached.) The key of course is having the transaction log backups - if the database is in simple mode and you've cycled through the log already, you're screwed. –  Brent Ozar May 15 '13 at 20:16
add comment

It could be that the transactions were rolled back. Or the rows were deleted by someone. You can use fn_dblog to read the transaction log to see what's in the log.

SQL Server didn't just lose the transactions. Your app either didn't write them correctly, or rolled them back, or they were deleted.

share|improve this answer
    
"SQL Server didn't just loose the transactions." this is the crux of the issue! The portion of application that inserts these transactions has not changed in over year if not longer. It is also inserting via stored procedure and noone can delete transactions in this database. I feel. SQL Server rolled them back after issuing OK return code to the application due to its own timeout issues, and didn't log an error but I cannot prove this....it is worst case scenario too. As it means it will happen again. Which is unacceptable for this instance. –  Dina May 15 '13 at 14:01
    
If SQL Server returns a message that the command was completed then it was. That means that it was written to the transaction log. There are no internal timeouts that would cause it to rollback the two messages besides that. Even if the database had gone offline and come back online at that point SQL Server would still have the records in the transaction log. You'll need to read through all the log backups from the time of the insert to verify they were inserted until you find the deletion commands for those records. The functions fn_dblog and db_dump_dblog will read the log and backups. –  mrdenny May 15 '13 at 14:54
    
I will have to use ApexSQL tool to read the log, because it is a specific table that has inserts on it daily. I must search the log for a specific ID that would have been on the 2 records that were supposed to be inserted. I don't think something this specific can be found using db_dump_dblog/fn_dblog. I'll update this Question for sure once the log shows if it even contains those 2 records & what happened to them! –  Dina May 15 '13 at 17:11
1  
As I have already commented: we had a similar situation. The server trace showed no sign of a rollback, delete or missing commit. Yet the rows weren't inserted. After changing the isolation level to serializable (nothing else was changed) this "vanishing" of rows never happened again. –  a_horse_with_no_name May 15 '13 at 21:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.