Communication link failure - SQL Server Forums

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

All Forums

General SQL Server Forums

Data Corruption Issues

Communication link failure

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-04-10 : 21:24:29

I get ODBC Error 823 when I try to access data from Crystal reports and I/O error, communication link failure...from query analyzer. But the weird thing is it is on and off. If I keep keep clicking 4 times in a row it happens 5 time or 7th time. It is alo only on one of the big tables. They are all stored on same primary filegroup?

Any ideas on what this might be? When I checked BOL it said disk error. The network team doesn't seem to agree. How can I make sure it is hardware or software problem?

Please help.

B.T.W we have SQL 2000 Enterprise.

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-04-11 : 10:33:54

The exact error is

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000005246cc000 in file 'L:\Program Files\Microsoft SQL Server\MSSQL$DATAETL\data\XYZ.mdf'.

Connection Broken

I ran DBCC check db and it did not return any errors. It is happening on one of the tables that I know and I ran DBCC CHECKFILEGROUP and returned the following error

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 457768688, index ID 0, page ID (1:2045495). The PageId in the page header = (1:2045519).

Please help!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-11 : 13:43:38

This is not a database corruption issue.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-04-11 : 14:23:25

Sounds like either a memory hardware issue, or your virus scan is getting in the way of reading pages into memory. Check to make sure that your virus scan is not doing on-access scans of your data files.

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-04-11 : 15:13:13

Thanks for the tip. I am finding it out. Will update when I know something.

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-04-19 : 13:48:37

We checked everything possible and does not look like it is virus issue. The strange thing is
It is intermittent.
Happening only on the biggest table of the db (20- 30 G)
DBCC returns no errors.
If I issue Select count(*) from myTable for a few times continously, it breaks.
I tried giving some indexes, removing them too.
None of the indexes are unique.
I reloaded a completely new table and renamed the bad table and the problem comes back even in new table.

We have very smart and experienced people on this site who bailed me out several times in the past.
Pls. help me again

TIA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 14:15:28

Have you seen this:
http://support.microsoft.com/kb/828339

What does SELECT @@VERSION show?

Please post the events from the application and system log in Event Viewer around the time that the error is encountered. Check Event Viewer on the database server and not on the client machine.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-19 : 18:11:31

Its an intermittent hardware failure - something in the IO path is corrupting pages intermittently in between the disk and the buffer pool. I'd run SQLIOSim (see http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx) to prove this.

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

SQLCode
Posting Yak Master

143 Posts

Posted - 2007-04-20 : 08:56:47

Thanks Tara and Paul:
I had seen the link Tara provided. I was trying to find out what caused it.
The error I provided is from the event viewer on the server. It is same from SQL analyzer and event viewer.

Paul, thanks for the link. I will try to get the approval to download and run it on the production server where this problem is occuring. But do you think not having indexes on big fact tables caused? Why is it happening only on big tables?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-20 : 11:11:31

My guess would be you're seeing it on the largest tables because they're the most frequently accessed tables. Having indexes on these tables cannot cause these problems.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources