[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

General network error.

Posted on 2006-04-01
32
Medium Priority
?
435 Views
Last Modified: 2009-12-16
What is this error all about????
I get the following error while running the query below:

Error Message:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken

Query:
select * from recipe_menus m
left join recipe_master r on r.rec_code=m.recipe
left join pop_trans_dtl_conso t on t.categ_2nd_code=m.categ2
where t.transaction_date>'2/28/2006'

Is this specific to the query or the sql server??
0
Comment
Question by:peterdevadoss
  • 16
  • 10
  • 6
32 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349582
Well it could be the query depending on the results and timeout settings, but it may be the server also. Try and reconnect to Query Analyzer and rerun, it may have just been a burp on the network. Also if you are able can you stop and restart hte SQL Service.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349598
IF those don't fix the problem you will need to look at your net libraries and your network connection. Are you connecting remotely to the server or running your query on the server itself? By remote I just mean are you using a QA that is connecting a server that is not on the box you are using Query Analyzer from? Try the query locally to the server and see what happens.

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349604
Connection with the server is broken. Try to ping the server and see the response.
If its ok then the query is taking logn time you need to have proper index on the table .
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:peterdevadoss
ID: 16349614
It seems to be problem with the data. I just find out that this error appears only for selected date range.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349617
Create following indexes and then try
Create nonclustered index IX_RECIPE_MENUS on RECIPE_MENUS(RECIPE)
GO
Create nonclustered index IX_recipe_master onrecipe_master (rec_code)
GO
Create nonclustered index IX_ pop_trans_dtl_conso  ON  pop_trans_dtl_conso  (categ_2nd_code)
GO
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349622
Thanks for agreeing about the first part Imran. That is why I suggested reconnect with query analyzer. It could have been a network burp breaking the connection.

Then without seeing the tables involved and the results involved I can't say if it is the query, but it could be a poorly performing query. There should at least be indexes on the columns that are being joined, and the transaction_date since you are sorting there, but before we jump to that I want to see if it is just a network issue that a bounce of the service or reconnect will fix..
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349623
CREATE ONE MORE INDEX
Create nonclustered index IX_RECIPE_MENUS on RECIPE_MENUS(categ2)
GO
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349628
Well before you do the indexes, do you already have indexes on those columns? How many results are you expecting in the date range specified? What is the table definition for the results being returned. What kind of columns are they? Any text or image type data?
0
 

Author Comment

by:peterdevadoss
ID: 16349637
I found out that problem with date '3/9/2006' .
I get this error when I run this query:
select * from pop_trans_dtl_conso where transaction_date='3/9/2006'
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349648
select * from recipe_menus m
left join recipe_master r on r.rec_code=m.recipe
left join pop_trans_dtl_conso t on t.categ_2nd_code=m.categ2
where t.transaction_date>'2/28/2006'


First a few performance notes in general about this query. It's always good syntax to prefix all references to objects by the owner. This actually makes a little less work for the query optimizer. Do you really need all of the columns returned (you are doing a Select * so asking for a lot of results).

Do you have indexes as Imran pointed out? I would also suggest an index on the transaction date if you are doing a lot of selects based on that. Potentially a good candidate for a clustered index because of the fact that you are searching through a range.

I would also preface the * with an M. not really a performance thing but just good practice.

Can you post the table definitions (script the tables and post the create tables) for the three tables involved so we can see the data types and sizes? Especially if this just happens with one range it can be a few things... 1.) THis range has the most data and is the poorest performing, 2.) There is some data in this range that is different than the other, etc. So seeing the tables would help.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349651
ok so you have narrowed it down to that table. Can you just post the table creation script for that one table?
0
 

Author Comment

by:peterdevadoss
ID: 16349669
Do you agree that there is a possibility of data corruption of that particular date.  That is my main concern. Because, this query was working until yesterday, without any cliches.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349681
That is a possibility, but it could just be that there is some data that is taking al ong time to return within that table. What happens if you run this:

select * from pop_trans_dtl_conso where transaction_date='3/10/2006'

and this

select * from pop_trans_dtl_conso where transaction_date='3/8/2006'

?

It could be some sort of corruption, it could also be that day has a lot more records, it could be someone entered a lot of data for one of them.

Try this :

select count(*) from pop_trans_dtl_conso where transaction_date = '3/9/2006'

then do the same for the day before and after. tell me the results from all of this.

Also post the columns and data types and lenghts for the data in this table. This will help out in troubleshooting.

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349732
As the data grows the query get slow and you get time out errors and depend upon the network connection you get such errors.
 
you should put the qualifier the owner of table as mike suggested and select only the needed columns and create index on the transaction date column  i.e

select column1, column2 from dbo.pop_trans_dtl_conso where transaction_date='3/9/2006'

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349738
imran - thanks for agreeing with each of the points I have previously mentioned. I am also curious from Peter to see the table data types. I am wondering if there are any improvements that can be made there.
0
 

Author Comment

by:peterdevadoss
ID: 16349755
select * from pop_trans_dtl_conso where transaction_date='3/10/2006'
result ok

select * from pop_trans_dtl_conso where transaction_date='3/8/2006'
result ok

select count(*) from pop_trans_dtl_conso where transaction_date = '3/9/2006'
Following Error message:
Server: Msg 3624, Level 20, State 1, Line 1
Location:       p:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression:       m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID:             52
Process ID:       792
Connection Broken

select count(*) from pop_trans_dtl_conso where transaction_date = '3/8/2006'
6224
select count(*) from pop_trans_dtl_conso where transaction_date = '3/9/2006'
6172
select count(*) from pop_trans_dtl_conso where transaction_date = '3/10/2006'
5173

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349758
Ok Mike Good Luck!
0
 

Author Comment

by:peterdevadoss
ID: 16349761
CREATE TABLE [POP_Trans_DTL_Conso] (
      [unik_key] [decimal](18, 0) NOT NULL ,
      [transaction_date] [datetime] NOT NULL ,
      [expr1] [decimal](18, 0) NULL ,
      [branch_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [stn_no] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [table_no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [staff_code_ot] [decimal](5, 0) NULL ,
      [order_no] [decimal](5, 0) NULL ,
      [date] [datetime] NULL ,
      [time_input_item] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [categ_2nd_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [prod_desc] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [categ_3rd_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [size] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [shape] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [qty] [decimal](8, 2) NULL ,
      [unit_price] [decimal](18, 4) NULL ,
      [void_refund] [decimal](1, 0) NULL ,
      [prod_stat] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [trans_stat] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [trans_type] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [prt_route] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [printed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [main_class] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [categ_1st_code] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_POP_Trans_DTL_Conso] PRIMARY KEY  CLUSTERED
      (
            [unik_key],
            [transaction_date],
            [branch_code]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO


0
 

Author Comment

by:peterdevadoss
ID: 16349787
select count(*) from pop_trans_dtl_conso
1832604
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16349802
Run DBCC CHECKDB and see what it reports. Do not have any users interacting
with the DB whilst you are running the DBCC.
and post the result
0
 

Author Comment

by:peterdevadoss
ID: 16349815
Is it gonna take long. Live Call Center is running on this database!!
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349821
hang on before doing that let me finish looking at your table.  I Just got here. DBCC Checkdb can take a while but there is a way to estimate the time first.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349833
RUN SELECT @@Version for me and paste the results here.
0
 

Author Comment

by:peterdevadoss
ID: 16349838
bythe way... what this DBCC Checkdb is gonna do?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349849
Go here
http://support.microsoft.com/?id=828337

This error is saying there is a possible problem with a data page. This is the way in which physical data is stored in SQL. DBCC CHECKDB is a consistency check for your database. It Checks the physical structure of your database looking for any corruption or inconsistencies. It checks and can repair in various modes.

Look this up in Books Online (SQL Servers help) go to the index and type in DBCC CHECKDB. Don't just run this on a reccomendation if you are not familiar with the options. Read up on it and get familiar with it. This needs to be done with users out of the system and create a backup first.

I think at this point you have a possibility of corruption. So you will need to see about getting an outage window. Definitely create a backup asap in case there are disks problems before they may spread.

DBCC CHECKCATALOG is something you should also run here. That too is in books online
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349850
You don't necessarily have to have your users out, by the way, but this is the safest and smoothest bet.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349868
So create a full backup of at least this database, not a bad idea to back up others especially any that share the disk where the datafiles in the filegroup(s) used by this table and it's indexes live.

Then run

DBCC CHECKDB(yourDatabase) first. this will report on any errors. List them back to us. You can also run

DBCC CHECKDB(yourDatabase) WITH PHYSICAL_ONLY this will just check physical corruption, will be a little faster, but run the first one. You are not repairing at this point, but put the results in a post here.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349897
You also should be on SQL Server Service Pack 4 (if you are using more than 4gb of memory on your server and have AWE enabled you will also need to apply a hotfix for the AWE, this is a trivial process).. Users should be out for this.

That error message you are getting is handled differently with SP4, you probably still have an underlying issue but you would receive a more informative error message most likely telling you to do a DBCC CHECKDB, so you have a few things you need to do..

Backup The database, apply the service pack, do a DBCC CHECKDB and take action on any issues found.
0
 

Author Comment

by:peterdevadoss
ID: 16349902
Mike problem is solved already.  When you told me to run the DBDC Checkup, I run in it with curiocity.  I had a second thought after few minutes and stoped it inbetween.

Wolla! I was able to run the query.

I dont know how this is happened.  May be I should open a new thred about this mystry !!!!

Any comments about this mystry!
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16349924
Well there are so many variables involved here.. Don't kick off a process like DBCC CHECKDB and stop it in the middle for future reference. Probably fine this time, but it could cause issue and a bad habit. Be sure you want to run it before doing so.

The error is gone which is good, it could have been a timing thing with contention on the disk from another process, it could be that there still are problems, etc. etc.

You still may have some underlying issues. Just because you don't see an error don't assume your server is error free. You should be running a DBCC checkdbs on a regular basis, ensuring your backups are working, checking free space, monitoring your sql logs, looking at index fragmentation etc. etc. on a regular basis otherwise you will have a sudden failure and be stuck.

You should be able to answer these questions, How long will it take to restore to a point of faiulre, how available is my server, what is the max load my server can handle and still perform well, what is the growth of my databases, when are my maintenance windows, is my backup strategy working with the business plans for disaster recovery, have I tested it and can I quickly restore if a failure happens, etc.

I think you still may have a problem, and you need to do a DBCC CHECKDB as soon as possible to ensure there are no issues with your physical data structure.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 200 total points
ID: 16349933
If you have any more problems with this error message please post here, if you have a new question please open a new question here. If you are not a DBA and your system has high availability requirements, I would suggest getting a DBA on staff or at least contracting with one to do remote checking of your systems. If you ever have another question and you want me to post to that question, my e-mail address is in my profile. I can't answer any questions through e-mail for this particular question as that violates EE member agreement but I would be happy to come to post a comment or discuss anything not related to this specific question.
0
 

Author Comment

by:peterdevadoss
ID: 16350056
Thank you very much Mike.  

I learned a lot from you and this is the begining for me.
Thank You again.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question