DataInconsistancy in SQL Server 2000 due to Long Running Transaction

Question

We are facing a situation in one of our production server. We have a particular Store procedure which perform an Insert operation on one of the biggest tables in the DB (It has more than a few million rows). This table is the most busy table in the DB and has many operations dependent upon it.

Recently we have been facing an issue on one particular production server.

We execute a Insert SP along with some other update SPs in one single transaction, and we are facing 'Long running transaction' issue for the Insert SP quite regularly. When ever we get this issue we find a typical behaviour in the data that gets inserted into the table. The datetime column value is getting inserted as 'null'. It happens some times for all rows and some time for a few rows. The datetime value is passed from the application. But the other update operations that are performed before and after the insert operation work well.

We ran sql profiler trace in our test environment(Not in the production server) but found that the datetime value was getting passed properly every time.

Also when we face the issue in production, we observe that:

1. @@trancount is equal to '0' but the DBCC OPENTRAN displays the particular open transaction.
2. The Last Wait type has value 'NETWORKIO'.
3. The Waittype is '0x0000'.
4. The Status is 'sleeping'.
5. The ISOLATION LEVEL is READ UNCOMMITTED.

So our concern is

1. Why the datetime is getting inserted as 'NULL' in this particular situation only?
2. How to avoid this situation and also the long running transaction?
3. What may be the cause for such situation to arise in one particular server?

Thanks in advance for the help,

Abhijit

Answer 1

There are a few things that I think need to be addressed simultaneously here. First, and most important, I would strongly suggest you take a look at that transaction to see what you can do to reduce the load it is putting on your system. Can it be broken up into multiple smaller transactions? Would the addition of better indexes help? Would capturing a subset of the data and putting it in a temp table reduce the number of SELECTS run against the main table? The list of questions here could go on for a while.

Next, look at the "application" that is kicking off the transaction. It is passing the dates in? If so, how is it doing it? If it is just passing in GetDate() then it is letting the SQL Server do the work. However, if the application is passing in a date value, I would make sure that this date value is ALWAYS valid. If it is, look at the table to be sure the formatting for the date is set up properly. For example, if your application passed in the European date format of 14-05-2011, your application might choke if it was expecting Month-Day-Year since 14 doesn't convert to a month.

Third, look through your tables to see if you have any triggers set up on them. If you do, look at each trigger very carefully. It is very possible that a trigger is causing a conflict within your transaction. Maybe you are writing data and your trigger is going back and updating the data (or it is evaluating the date and saying it is invalid - see example above).

Fourth, check the data before it leaves the transaction. Read it when it comes in and read it after the INSERT has been performed. Maybe there is a client operation going on after the transaction is complete that wipes out the date value.

Finally, you need to look at your test environment. If this works in test but it doesn't in production, there is something different between the two systems. That is the cause, whether directly or indirectly. Maybe it is hardware related (bad RAM?) or maybe it is something different in the settings (Locking, the fact that clients are attempting to perform actions against the data, etc.)

Beyond this, here is a link to a forum with some other potential answers for you:

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/1063b65df1f97492/8649bee2002646a2