x

DB Error not found by DBCC

Hi all, I run an accounting package with SS 2008R2 as the DB. Today we started having an odd issue withe the accounting package - it hangs on any attempts to insert into one of the tables. I run check DB every other day and do DB index maintenance weekly (rebuild or reorganize or update statistics, depending on fragmentation and page count). I cloned the live DB into a test DB to play with and attempt more aggressive diagnostics as well as evaluate repairs and guess what - the DB cloned fine (backup DB and tlog, restore DB and apply tlog) and the cloned DB works perfectly. My solution is likely to be drop the live DB and restore it but I am uneasy not understanding what went wrong here. Any ideas on what failed and how I can automatically detect it moving forward?

In investigating this I did track down the sessions involved and noted that two session were involved: 1) a select statement reading the row to be updated 2) an update statement for the row.

The second session was waiting for the first to complete. The first session was hung on async_network_io.

I am aware that async_network_io usually means the client has not finished reading a result set but in this case the result set is a single row, and in testing I was the only user. As part of testing after hours I disconnected all remote sessions and ran the same update on the server itself so I know that the single row being retrieved across the network was not the issue.

cheers ram
more ▼

asked Jul 07 '11 at 05:32 PM in Default

RamAM gravatar image

RamAM
83 3 3 4

What type of package is it (SSIS, custom script, vendor provided)?

What error is actually displaying when this package "hangs" on the table it is run against, or do you get an error back?

Could you possible provide the statement it is using to update that table? I am not strong on T-SQL but plenty of other folks on this site are.
Jul 07 '11 at 06:16 PM Shawn_Melton
The software in question is an off the shelf piece of accounting software. The statement that blocks has worked properly at least 50,000 times in the last year and is essentially : Update table set status='Ready' where docid='IN000234234'. DocID is indexed and unique. The application hangs waiting for response.
Jul 07 '11 at 06:53 PM RamAM
Have you checked the active transactions in SQL Server to see what it seems to be doing with that statement? How large is the table it is trying to update?
Jul 07 '11 at 07:11 PM Shawn_Melton
The table is not too big , about 50,000 rows and a dozen columns. When running afters there was no real other activity (no locks other than the ones these two sessions took and no blocked transactions - both according to the native reports)
Jul 07 '11 at 07:21 PM RamAM

I recall having issues with a 3rd party app that was reading from a table and writing back to it in different sessions. I believe we had to add NOLOCK to the SELECT queries so it would not lock the record the other was trying to update. My T-SQL skills are not there yet so I'm just trying offer suggestions.

You had said it has worked previously, have you compared those scripts to this one to see if they may have changed their code or something?
Jul 07 '11 at 08:15 PM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest
I ultimately resolved the issue by doing an application level backup and restore. For whatever reason doing a DB+Log backup and restore into a second DB in the same instance gave me a working environment but doing a DB+Log backup and restore into the primary DB (same instance) did not change anything, however, using the accounting packages full-export full-import back into the primary DB did work. I still don't know what the cause or problem was so I'm unhappy with the resolution. I am happy that everyone is working as usual today. I am still very much interested in debugging this though my ability to extract new data is limited since I can't reproduce the problem using the backups I took.
more ▼

answered Jul 08 '11 at 09:43 AM

RamAM gravatar image

RamAM
83 3 3 4

(comments are locked)
10|1200 characters needed characters left

If you can post the actual error then it will help a lot but this sounds to me like a locking issue. The test system will not have the same users logged in etc and so the locking is not occurring.

I would start off by running sp_whoisactive to see the TSQL being executed at the time of the error and work on from there.

This is not a database consistency issue - DBCC checks the integrity of the data, it is much more like a programmatic issue
more ▼

answered Jul 08 '11 at 04:03 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

There was no error per se - the client application would just hang until killed. Everything was fine except insert/update in one table which represents about 10% of what we use the system for so I had the users not do any insert/update that relate to that table (clear division from the UI) until after hours. After hours I was the only user in the DB so there was no contention issue around locks. I used the native "show blocks" and "show locks" reports in combination with sp_who and dm_os_wait* to see what was going on. Per above there were two sessions.. one doing an update (blocked on the other)... the other doing a select (waiting on ASYNC_NETWORK_IO)... I tried this by using the app locally on the DB box so ASYNC_NETWORK_IO was unexpected [I should probably be using SHMEM or something for local activity but I don't normally use anything like that locally on the DB!].
Jul 08 '11 at 09:38 AM RamAM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x562
x50

asked: Jul 07 '11 at 05:32 PM

Seen: 904 times

Last Updated: Jul 07 '11 at 07:35 PM