General network error.

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??
peterdevadossAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeWalshCommented:
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
MikeWalshCommented:
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
imran_fastCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

peterdevadossAuthor Commented:
It seems to be problem with the data. I just find out that this error appears only for selected date range.
0
imran_fastCommented:
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
MikeWalshCommented:
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
imran_fastCommented:
CREATE ONE MORE INDEX
Create nonclustered index IX_RECIPE_MENUS on RECIPE_MENUS(categ2)
GO
0
MikeWalshCommented:
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
peterdevadossAuthor Commented:
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
MikeWalshCommented:
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
MikeWalshCommented:
ok so you have narrowed it down to that table. Can you just post the table creation script for that one table?
0
peterdevadossAuthor Commented:
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
MikeWalshCommented:
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
imran_fastCommented:
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
MikeWalshCommented:
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
peterdevadossAuthor Commented:
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
imran_fastCommented:
Ok Mike Good Luck!
0
peterdevadossAuthor Commented:
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
peterdevadossAuthor Commented:
select count(*) from pop_trans_dtl_conso
1832604
0
imran_fastCommented:
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
peterdevadossAuthor Commented:
Is it gonna take long. Live Call Center is running on this database!!
0
MikeWalshCommented:
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
MikeWalshCommented:
RUN SELECT @@Version for me and paste the results here.
0
peterdevadossAuthor Commented:
bythe way... what this DBCC Checkdb is gonna do?
0
MikeWalshCommented:
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
MikeWalshCommented:
You don't necessarily have to have your users out, by the way, but this is the safest and smoothest bet.
0
MikeWalshCommented:
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
MikeWalshCommented:
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
peterdevadossAuthor Commented:
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
MikeWalshCommented:
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
MikeWalshCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peterdevadossAuthor Commented:
Thank you very much Mike.  

I learned a lot from you and this is the begining for me.
Thank You again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.