consistency errors

Can somebody explain to me what is a consistency error and
how does it happens. Is there a way to prevent them ?A consistency error, in its most general form, is when an inconsistency
exists, based on some rules.
In SQL Server, a consistency error is when the structural or logical
integrity of the database or a table within the database has been broken.
Some examples:
1) page X in the database thinks its allocated to table Y, but scanning
table Y does not show any links to page X.
2) A record in the clustered index for table A does not have exactly one
matching record in non-clustered index B on table A
3) A record in the clustered index for table J has a text/image column with
timestamp K, but no text was found with timestamp K in the text index for
table J
There are literally hundreds of such consistency rules inside SQL Server,
all of which are verified with the DBCC CHECKDB command (and related
commands).
Such consistency errors are usually caused by bad hardware corrupting pages
in the database. Given that there's no way to prevent hardware going bad,
the issue is really how to ensure you can recover from a hardware-caused
corruption with minimal downtime and data loss. This involves having a
disaster recovery plan and a solid backup strategy.
I don't have pointers to hand to the existing best-practices docs and KB
articles but I'm sure an MVP will reply-group with them.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pollus" <anonymous@.discussions.microsoft.com> wrote in message
news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> Can somebody explain to me what is a consistency error and
> how does it happens. Is there a way to prevent them ?|||As Paul has already explained some issues, especially
hardware problems, can't be totally prevented. You want to
monitor your systems to stay on top of any potential
problems which can arise. Chapter 4 in the Operations Guide
on TechNet has some good checks, recommended frequencies,
info on backups, availability, etc:
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops4.asp
Also related would be the 2003 August and September issues
of SQL Magazine had several articles on disaster prevention,
disaster recovery:
www.sqlmag.com
-Sue
On Mon, 24 Nov 2003 12:12:09 -0800, "Pollus"
<anonymous@.discussions.microsoft.com> wrote:
>Can somebody explain to me what is a consistency error and
>how does it happens. Is there a way to prevent them ?|||> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
Below is my general recommendations (after going though it in the MVP group). I don't have KB
pointers, though...
Here are the general recommendations for handling a suspect or corrupt database:
0. Ensure you have a backup strategy that you can use to recover from hardware failures (including
corruption). I recommend performing both database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB
for those errors. You don't want this to happen again! If the database is suspect, the file might
have been in use by for instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this
will often result in loss of data. Additional solutions, depending on the errors, may be to manually
rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect" the database using
sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is
too damaged, it might just pop back to suspect again. There's also something called "emergency mode"
which is a "panic" status you can set in order to try to get data out of a damaged database. I think
the name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eQgnZsssDHA.3436@.tk2msftngp13.phx.gbl...
> A consistency error, in its most general form, is when an inconsistency
> exists, based on some rules.
> In SQL Server, a consistency error is when the structural or logical
> integrity of the database or a table within the database has been broken.
> Some examples:
> 1) page X in the database thinks its allocated to table Y, but scanning
> table Y does not show any links to page X.
> 2) A record in the clustered index for table A does not have exactly one
> matching record in non-clustered index B on table A
> 3) A record in the clustered index for table J has a text/image column with
> timestamp K, but no text was found with timestamp K in the text index for
> table J
> There are literally hundreds of such consistency rules inside SQL Server,
> all of which are verified with the DBCC CHECKDB command (and related
> commands).
> Such consistency errors are usually caused by bad hardware corrupting pages
> in the database. Given that there's no way to prevent hardware going bad,
> the issue is really how to ensure you can recover from a hardware-caused
> corruption with minimal downtime and data loss. This involves having a
> disaster recovery plan and a solid backup strategy.
> I don't have pointers to hand to the existing best-practices docs and KB
> articles but I'm sure an MVP will reply-group with them.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Pollus" <anonymous@.discussions.microsoft.com> wrote in message
> news:01e201c3b2c7$3d7af450$a501280a@.phx.gbl...
> > Can somebody explain to me what is a consistency error and
> > how does it happens. Is there a way to prevent them ?
>

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
....
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
....
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
....
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
Martin
The repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>
sqlsql

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
...
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
...
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
...
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
MartinThe repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>

Consistency error using DBCC CheckDB

Hi,
when I run DBCC CHECKDB against one of my call databases it tells me there
are two consistency errors and 0 allocation errors. The minimum repair
option is REPAIR_REBUILD.
So I have my db in single user mode and I execute:
dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
this returns:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '17847'.
DBCC results for 'Leigh_Call2'.
...
DBCC results for 'CallDataItemEnumeratedStaging'.
There are 201627 rows in 2000 pages for object
'CallDataItemEnumeratedStaging'.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
reference from previous page (1:21981). Possible chain linkage problem.
...
DBCC results for 'CallEventStaging'.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
There are 178358 rows in 1487 pages for object 'CallEventStaging'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CallEventStaging' (object ID 1106102981).
...
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Leigh_Call2'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (Leigh_Call2 repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The statement has been terminated.
This seems to be the error portion of the results returned.
Each time I run it the name of the table with the problem changes.
Is this pointing to perhaps some physical corruption on the disks?
Info, views and opinions appreciated.
MartinThe repair is trying to rebuild the index - obviously that's failing because
of the duplicate key violation.
My guess as to what's causing dbcc to report these issues are stale reads
from your disk controller and there's an active workload on the database
that's causing page splits in various tables. I bet if you shut everything
down and power it back up then a checkdb will come back clean for the
database. If so, you need to run diagnostics on your IO subsystem and make
sure all the firmware is up to date.
Thanks
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"Martin Selway" <martin.selway@.csdss.com> wrote in message
news:uxahkybOHHA.3900@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when I run DBCC CHECKDB against one of my call databases it tells me there
> are two consistency errors and 0 allocation errors. The minimum repair
> option is REPAIR_REBUILD.
> So I have my db in single user mode and I execute:
> dbcc checkdb ('Leigh_Call2', REPAIR_REBUILD) WITH ALL_ERRORMSGS
> this returns:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '17847'.
> DBCC results for 'Leigh_Call2'.
> ...
> DBCC results for 'CallDataItemEnumeratedStaging'.
> There are 201627 rows in 2000 pages for object
> 'CallDataItemEnumeratedStaging'.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. B-tree chain linkage
> mismatch. (1:21981)->next = (1:21983), but (1:21983)->Prev = (1:22065).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1106102981, index ID 1. Page (1:22065) is missing a
> reference from previous page (1:21981). Possible chain linkage problem.
> ...
> DBCC results for 'CallEventStaging'.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> There are 178358 rows in 1487 pages for object 'CallEventStaging'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'CallEventStaging' (object ID 1106102981).
> ...
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Leigh_Call2'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (Leigh_Call2 repair_rebuild).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The statement has been terminated.
> This seems to be the error portion of the results returned.
> Each time I run it the name of the table with the problem changes.
> Is this pointing to perhaps some physical corruption on the disks?
> Info, views and opinions appreciated.
> Martin
>

Consistency error in sql2000

Hi,
I just found a lot of these errors in the errorlog and eventlog:
2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
repair option). This returned:
There are 4889992 rows in 70072 pages for object 'XXXXX'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'XXXXX' (object ID 1781581385).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (XXXXXX ).
Not looking good at all.
Then I ran a dbcc checkdb ('yyyyy') with this result
...
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'yyyyy'.
A new dbcc checktable reported no errors.
How could this be. Was this a transient error after all?
Any insight is appreciated.
TIA
It is quite possible to run into transient DBCC errors in SQL2000, though
it's pretty rare. If your database is not huge, I'd re-run DBCC CHECKDB when
there isn't much activity. If that returns clean, you are probably okay.
Transient errors are more lickly when there are heavy activities.
Linchi
"Gurba" wrote:

> Hi,
> I just found a lot of these errors in the errorlog and eventlog:
> 2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
> 2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
> in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
> I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
> repair option). This returned:
> There are 4889992 rows in 70072 pages for object 'XXXXX'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'XXXXX' (object ID 1781581385).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (XXXXXX ).
> Not looking good at all.
> Then I ran a dbcc checkdb ('yyyyy') with this result
> ...
> ...
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'yyyyy'.
> A new dbcc checktable reported no errors.
> How could this be. Was this a transient error after all?
> Any insight is appreciated.
> TIA
>

Consistency error in sql2000

Hi,
I just found a lot of these errors in the errorlog and eventlog:
2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
repair option). This returned:
There are 4889992 rows in 70072 pages for object 'XXXXX'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'XXXXX' (object ID 1781581385).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (XXXXXX ).
Not looking good at all.
Then I ran a dbcc checkdb ('yyyyy') with this result
...
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'yyyyy'.
A new dbcc checktable reported no errors.
How could this be. Was this a transient error after all?
Any insight is appreciated.
TIAIt is quite possible to run into transient DBCC errors in SQL2000, though
it's pretty rare. If your database is not huge, I'd re-run DBCC CHECKDB when
there isn't much activity. If that returns clean, you are probably okay.
Transient errors are more lickly when there are heavy activities.
Linchi
"Gurba" wrote:

> Hi,
> I just found a lot of these errors in the errorlog and eventlog:
> 2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
> 2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
> in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
> I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
> repair option). This returned:
> There are 4889992 rows in 70072 pages for object 'XXXXX'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'XXXXX' (object ID 1781581385).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (XXXXXX ).
> Not looking good at all.
> Then I ran a dbcc checkdb ('yyyyy') with this result
> ...
> ...
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'yyyyy'.
> A new dbcc checktable reported no errors.
> How could this be. Was this a transient error after all?
> Any insight is appreciated.
> TIA
>

Consistency error in sql2000

Hi,
I just found a lot of these errors in the errorlog and eventlog:
2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
repair option). This returned:
There are 4889992 rows in 70072 pages for object 'XXXXX'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'XXXXX' (object ID 1781581385).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (XXXXXX ).
Not looking good at all.
Then I ran a dbcc checkdb ('yyyyy') with this result
...
...
CHECKDB found 0 allocation errors and 0 consistency errors in database
'yyyyy'.
A new dbcc checktable reported no errors.
How could this be. Was this a transient error after all?
Any insight is appreciated.
TIAIt is quite possible to run into transient DBCC errors in SQL2000, though
it's pretty rare. If your database is not huge, I'd re-run DBCC CHECKDB when
there isn't much activity. If that returns clean, you are probably okay.
Transient errors are more lickly when there are heavy activities.
Linchi
"Gurba" wrote:
> Hi,
> I just found a lot of these errors in the errorlog and eventlog:
> 2006-04-01 20:55:13.84 spid83 Error: 605, Severity: 21, State: 1
> 2006-04-01 20:55:13.84 spid83 Attempt to fetch logical page (1:420416)
> in database 'confirm' belongs to object '6488161', not to object 'XXXXX'..
> I did as the BOL suggests and ran a dbcc checktable ('XXXXX') (with no
> repair option). This returned:
> There are 4889992 rows in 70072 pages for object 'XXXXX'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'XXXXX' (object ID 1781581385).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (XXXXXX ).
> Not looking good at all.
> Then I ran a dbcc checkdb ('yyyyy') with this result
> ...
> ...
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'yyyyy'.
> A new dbcc checktable reported no errors.
> How could this be. Was this a transient error after all?
> Any insight is appreciated.
> TIA
>sqlsql

Consistency Error in Checkdb

When I use DBCC CHECKDB I got allocation errors and
consistency errors. I use CHECKDB with
repair_allow_data_loss to fix it. It can fix the
allocation errors, but the consistency errors are still
there. How can I fix consistency errors?
Thanks,
Monica.First you need to check if you have any hardware issues or not. If you have any hardware problem you need to fix them first. If the number of errors don't happen to reduce, you can then mark the database in emergency mode and DTS the objects and data to a new database on the server. Once the transfer completes, make sure you run DBCC CHECKDB on the new database. Once it comes out clean, drop the problem database, rename the new database to the old one
This posting is provided "AS IS" with no warranties, and confers no rights.
http://www.microsoft.com/info/cpyright.ht|||Monica,
It depends what the errors are and which objects they are in. A very small
number of errors on critical tables cannot be repaired, in which case
you'll need to restore from your backups or extract as much information as
you can as suggested in the other reply.
Microsoft recommends that you have a backup strategy that allows you to
recover from hardware-caused corruptions rather than using the
REPAIR_ALLOW_DATA_LOSS option to CHECKDB, which as its name implies may have
to delete data to get the database back to a structurally consistent state.
If you're still stuck, please call Product Support
(http://support.microsoft.com) who will be able to assist you.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Monica Clinton" <hv37@.yahoo.com> wrote in message
news:2fe501c3e1d1$9aef89f0$a001280a@.phx.gbl...
> When I use DBCC CHECKDB I got allocation errors and
> consistency errors. I use CHECKDB with
> repair_allow_data_loss to fix it. It can fix the
> allocation errors, but the consistency errors are still
> there. How can I fix consistency errors?
> Thanks,
> Monica.|||Thanks for you help.
We fixed the hardware problem, now we are trying to fix
the data. How do I mark the database in emergency mode?
When we DTS the data to a new database, we had the same
errors. How to fix the errors?
Thank you.
Monica
>--Original Message--
>First you need to check if you have any hardware issues
or not. If you have any hardware problem you need to fix
them first. If the number of errors don't happen to
reduce, you can then mark the database in emergency mode
and DTS the objects and data to a new database on the
server. Once the transfer completes, make sure you run
DBCC CHECKDB on the new database. Once it comes out clean,
drop the problem database, rename the new database to the
old one.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>http://www.microsoft.com/info/cpyright.htm
>
>.
>

Consistency Error in Checkdb

When I use DBCC CHECKDB I got allocation errors and
consistency errors. I use CHECKDB with
repair_allow_data_loss to fix it. It can fix the
allocation errors, but the consistency errors are still
there. How can I fix consistency errors?
Thanks,
Monica.First you need to check if you have any hardware issues or not. If you have
any hardware problem you need to fix them first. If the number of errors don
't happen to reduce, you can then mark the database in emergency mode and DT
S the objects and data to a
new database on the server. Once the transfer completes, make sure you run D
BCC CHECKDB on the new database. Once it comes out clean, drop the problem d
atabase, rename the new database to the old one.
This posting is provided "AS IS" with no warranties, and confers no rights.
http://www.microsoft.com/info/cpyright.htm|||Monica,
It depends what the errors are and which objects they are in. A very small
number of errors on critical tables cannot be repaired, in which case
you'll need to restore from your backups or extract as much information as
you can as suggested in the other reply.
Microsoft recommends that you have a backup strategy that allows you to
recover from hardware-caused corruptions rather than using the
REPAIR_ALLOW_DATA_LOSS option to CHECKDB, which as its name implies may have
to delete data to get the database back to a structurally consistent state.
If you're still stuck, please call Product Support
(http://support.microsoft.com) who will be able to assist you.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Monica Clinton" <hv37@.yahoo.com> wrote in message
news:2fe501c3e1d1$9aef89f0$a001280a@.phx.gbl...

quote:

---

> When I use DBCC CHECKDB I got allocation errors and
> consistency errors. I use CHECKDB with
> repair_allow_data_loss to fix it. It can fix the
> allocation errors, but the consistency errors are still
> there. How can I fix consistency errors?
> Thanks,
> Monica.

---

|||Thanks for you help.
We fixed the hardware problem, now we are trying to fix
the data. How do I mark the database in emergency mode?
When we DTS the data to a new database, we had the same
errors. How to fix the errors?
Thank you.
Monica

quote:

---

>--Original Message--
>First you need to check if you have any hardware issues

---

or not. If you have any hardware problem you need to fix
them first. If the number of errors don't happen to
reduce, you can then mark the database in emergency mode
and DTS the objects and data to a new database on the
server. Once the transfer completes, make sure you run
DBCC CHECKDB on the new database. Once it comes out clean,
drop the problem database, rename the new database to the
old one.

quote:

---

>This posting is provided "AS IS" with no warranties, and

---

confers no rights.

quote:

---

>http://www.microsoft.com/info/cpyright.htm
>
>.
>

---

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RI
D
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Proble
ms
http://support.microsoft.com/defaul...kb;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
RonYou'd do best to call CSS to get the exact answer.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/defaul...kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/defaul...kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RID
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
RonYou'd do best to call CSS to get the exact answer.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron

Consequences of Error 644?

My customer has encountered Error 644 (Could not find the index entry for RID
'%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
looked at the following KB entries:
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
http://support.microsoft.com/default...b;en-us;826433
FIX: You receive a 644 error message when you run an UPDATE statement and
the isolation level is set to READ UNCOMMITTED
http://support.microsoft.com/?kbid=834290
I need help in interpreting this error for my customer. What they want to
know is:
1) Is this indicative of a larger problem?
2) Is it likely to happen repeatedly?
3) Is there a solution besides the available hotfix (since a hotfix is
always a scary thing to apply to a production system)?
Is there anything beyond what is offered in the above KB articles that can
help my customer?
Thanks,
Ron
You'd do best to call CSS to get the exact answer.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default...b;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron
|||If you get the errr while running DBCC - The error is telling you that a row
exists in a table, but the index entry for some index is missing... This
could be a transient problem, - one that occurs because you are running
DBCCs while users are making changes.. re-run DBCC on the table. If the
error moves to a different row or a different table, it is just a transient
error due to timing... However if the same error shows up in the same
location - it is a hard error and must be addressed.
You can drop and re-create the index, since the index is created from the
table.
If you get the error while running and UPdate statement, the KB article you
mention provideds the details. It says that the problem is a SQL Server
software problem, and will not lead to corruption. If it is this case, it
will happen repeatedly when the customer does the same work which caused the
issue originally... I would apply the hotfix - but I would first open a
call to PSS ($249) - to get their advice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RonTop" <RonTop@.discussions.microsoft.com> wrote in message
news:DC27912A-11CA-4084-959B-2F414F7DD27E@.microsoft.com...
> My customer has encountered Error 644 (Could not find the index entry for
RID
> '%.*hs' in index page %S_PGID, index ID %d, database '%.*ls'.). I have
> looked at the following KB entries:
> PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O
Problems
> http://support.microsoft.com/default...b;en-us;826433
> FIX: You receive a 644 error message when you run an UPDATE statement and
> the isolation level is set to READ UNCOMMITTED
> http://support.microsoft.com/?kbid=834290
> I need help in interpreting this error for my customer. What they want to
> know is:
> 1) Is this indicative of a larger problem?
> 2) Is it likely to happen repeatedly?
> 3) Is there a solution besides the available hotfix (since a hotfix is
> always a scary thing to apply to a production system)?
> Is there anything beyond what is offered in the above KB articles that can
> help my customer?
> Thanks,
> Ron
sqlsql

connot connect to the repository with Analysis Service

I have a problem with Analysis Services.

When I open Analysis Manager and try to connect to the SQL server with Analysis Service, the following error message is displayed:

"connot connect to the repository. Error:Could not use ''; file already in use."

the server and my local machine all both using SQL server 2000 with SP4

any help will be appreciate, thanks in advance.

Benjamin

Looks like your repository is not migrated to SQL Server, but still in msmdrep.mdb file. Do you have enough permissions to open it ? Do you have access to $MsOlapRepository share ?

Connects but network error, Im behind Wingate

Hello.

I'm trying to connect to SQL Server using Enterprise manager. I'm behind a Wingate proxy.

I've configured an alias using TCP/IP, pointing to the (local network) IP of the Wingate box.

On the Wingate box I've set a TCP Mapping service on port 1433, pointing to the IP of the remote SQL Server on port 1433 too.

The error message is:
General network error. Changed language settings to us-english. ConnectionRead(Error SIO_KEEPALIVE_VALS())..

I assume at least *something* is right, as if I change the password to a bogus one I get "Login failed" instead of this network error.

Hope someone can point me in the right direction. Thanks,
- ManuelYou probably need to enable UDP port 1434 as well. See MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1zuc.asp) for a more complete explaination.

-PatP|||Hello.

Just added UDP mapping on 1434. Same error.

Read the MSDN article but couldn't figure what else to try. Just in case I didn't make myself clear, I'm trying to connect to a server outside my local network (as opposed to enabling a server on my network to be seen on the internet).

How can I debug this forward? What else can I do?

Thanks again,
- Manuel|||Check the Wingate log, and look to see what traffic is blocked from the workstation headed toward the server's IP address. It should be on port 1433 unless you are using a named instance of SQL Server, but look for anything, just in case.

Triple check to be sure that your Client Network Settings (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_client_7v03.asp) and the server's Server Network Settings (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_2epl.asp) are compatible, especially if they are using a Proxy (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_1y5u.asp).

-PatP|||As referred by Pat ensure both client & server's netlibs are similar, by chance are you running on cluster?

Try to use Named-pipes instead of Tcp/IP.

Information about Orphaned sessions (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_rptconsetup_5gmr.asp) from this mSDN link.

HTH

Connectivity problem-please read

Hi,
I have 3 SQL servers 7, and since yesterday, some of my
clients started receiving different error messages in the
middle of doing something like reporting, or editing,
however when i checked the event logs on all servers,
there is no error message there. all different messages
include a "General network error" for example one of them
is:
SQL Server Message 10054, Possible network error Write to
SQL Server Failed. General Network Error. Check your
documentation. Debugging info: sqlCompile...
Does anyone have any idea how to deal with this problem, I
am monitoring different switches but cannot see any
problem. Also checked ODBC settings all fine, and we have
these setting for a couple of years with no problem.
Thanks in advance for any help-RobHi Rob,
10054 is usually seen when the underlying tcp session is dropped. The
only way to tshoot this is to make network traces from the client capturing
the problem. Then work with your network staff to resolve them. More than
likely, the cause is a dropped tcp session.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Can you explain a bit more, How can I make the Network
trace? Basically, I can not see any problem with the
network, what kind of network problems should I looking
for?
Thanks a lot-Rob
>--Original Message--
>Hi Rob,
> 10054 is usually seen when the underlying tcp session
is dropped. The
>only way to tshoot this is to make network traces from
the client capturing
>the problem. Then work with your network staff to
resolve them. More than
>likely, the cause is a dropped tcp session.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||Hi Rob,
So all SQL connections from clients are supported by underlying tcp or
smb sessions. ODBC and OLEdb applications rely on the integrity of the
underlying network session. If the session is torn down, then the
application will fail. In our case we tend to report "general network
Error".
So, if this is the error you're seeing, you need to make network traces
from the client and see if the underlying tcp or smb session was dropped.
Then work with your network team to resolve it.
Otherwise, you can make the traces and open a case with us to help do the
review.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sqlsql

Connectivity problem-please read

Hi,
I have 3 SQL servers 7, and since yesterday, some of my
clients started receiving different error messages in the
middle of doing something like reporting, or editing,
however when i checked the event logs on all servers,
there is no error message there. all different messages
include a "General network error" for example one of them
is:
SQL Server Message 10054, Possible network error Write to
SQL Server Failed. General Network Error. Check your
documentation. Debugging info: sqlCompile...
Does anyone have any idea how to deal with this problem, I
am monitoring different switches but cannot see any
problem. Also checked ODBC settings all fine, and we have
these setting for a couple of years with no problem.
Thanks in advance for any help-Rob
Hi Rob,
10054 is usually seen when the underlying tcp session is dropped. The
only way to tshoot this is to make network traces from the client capturing
the problem. Then work with your network staff to resolve them. More than
likely, the cause is a dropped tcp session.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Kevin,
Can you explain a bit more, How can I make the Network
trace? Basically, I can not see any problem with the
network, what kind of network problems should I looking
for?
Thanks a lot-Rob
>--Original Message--
>Hi Rob,
> 10054 is usually seen when the underlying tcp session
is dropped. The
>only way to tshoot this is to make network traces from
the client capturing
>the problem. Then work with your network staff to
resolve them. More than
>likely, the cause is a dropped tcp session.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>
|||Hi Rob,
So all SQL connections from clients are supported by underlying tcp or
smb sessions. ODBC and OLEdb applications rely on the integrity of the
underlying network session. If the session is torn down, then the
application will fail. In our case we tend to report "general network
Error".
So, if this is the error you're seeing, you need to make network traces
from the client and see if the underlying tcp or smb session was dropped.
Then work with your network team to resolve it.
Otherwise, you can make the traces and open a case with us to help do the
review.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connectivity problem

Hi,
I have 3 SQL servers 7, and since yesterday, some of my
clients started receiving different error messages in the
middle of doing something like reporting, or editing.
(they run different applications).
However when i checked the event logs on all servers there
is no error message there. all different messages include
a "General network error" for example one of them is:
SQL Server Message 10054, Possible network error Write to
SQL Server Failed. General Network Error. Check your
documentation. Debugging info: sqlCompile...
Usually if I reboot their WS, thats temperary fixs their
problem.
Does anyone have any idea how to deal with this problem, I
am monitoring different switches but cannot see any
problem. Also checked ODBC settings all fine, and we have
these setting for a couple of years with no problem.
Thanks in advance for any help-RobAre the errors occurring on all 3 SQL Servers or just one SQL Server?
How is the health of the network? Are the switches in good working order?
How about the NIC's in the SQL Server machines? Is your DNS acting up?
What service packs have you applied recently to any equipment in the process
(Client, Server, routers/switches etc.).
What has changed in the last few weeks?
Rick Sawtell
MCT, MCSD, MCDBA
"Rob" <Rob@.nul.ca> wrote in message
news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
> Hi,
> I have 3 SQL servers 7, and since yesterday, some of my
> clients started receiving different error messages in the
> middle of doing something like reporting, or editing.
> (they run different applications).
> However when i checked the event logs on all servers there
> is no error message there. all different messages include
> a "General network error" for example one of them is:
> SQL Server Message 10054, Possible network error Write to
> SQL Server Failed. General Network Error. Check your
> documentation. Debugging info: sqlCompile...
> Usually if I reboot their WS, thats temperary fixs their
> problem.
> Does anyone have any idea how to deal with this problem, I
> am monitoring different switches but cannot see any
> problem. Also checked ODBC settings all fine, and we have
> these setting for a couple of years with no problem.
> Thanks in advance for any help-Rob
>|||On 2 of them. The network health is good and I can not see
any problem. As long as I am aware, we havent changed
anything recently and this problem startd since a couple
days ago.
Rob

>--Original Message--
>Are the errors occurring on all 3 SQL Servers or just one
SQL Server?
>How is the health of the network? Are the switches in
good working order?
>How about the NIC's in the SQL Server machines? Is your
DNS acting up?
>What service packs have you applied recently to any
equipment in the process
>(Client, Server, routers/switches etc.).
>What has changed in the last few weeks?
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Rob" <Rob@.nul.ca> wrote in message
>news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
the[vbcol=seagreen]
there[vbcol=seagreen]
include[vbcol=seagreen]
to[vbcol=seagreen]
problem, I[vbcol=seagreen]
have[vbcol=seagreen]
>
>.
>|||Hi Bob,
Thanks for using MSDN Managed Newsgroup!
From your descriptions, I understood that your SQL7 received different
error and General Network Error. Have I understood you? If their is
anything I misunderstood, please feel free to let me know.
First of all, Would you please show me a detailed descriptions of what kind
of error message it will show? Is it possible for you to paste your latest
error log here?
Secondly, based on my scope, "General network error" doesn't mean a network
issue in most cases, it is a very common error that I am afraid we need
more time troubleshooting.
Thirdly, you said it will temperarily fix the problem by rebooting. How
long after rebooting will this kind of error occurs again?
Last but not the least, if it is an urgent one, you can contact Microsoft
Product Support directly to discuss additional support options you may have
available, by contacting us at 1-(800)936-5800 or by choosing one of the
options listed at
http://support.microsoft.com/defaul...d=sz;en-us;top.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!sqlsql

Connectivity problem

Hi,
I have 3 SQL servers 7, and since yesterday, some of my
clients started receiving different error messages in the
middle of doing something like reporting, or editing.
(they run different applications).
However when i checked the event logs on all servers there
is no error message there. all different messages include
a "General network error" for example one of them is:
SQL Server Message 10054, Possible network error Write to
SQL Server Failed. General Network Error. Check your
documentation. Debugging info: sqlCompile...
Usually if I reboot their WS, thats temperary fixs their
problem.
Does anyone have any idea how to deal with this problem, I
am monitoring different switches but cannot see any
problem. Also checked ODBC settings all fine, and we have
these setting for a couple of years with no problem.
Thanks in advance for any help-RobAre the errors occurring on all 3 SQL Servers or just one SQL Server?
How is the health of the network? Are the switches in good working order?
How about the NIC's in the SQL Server machines? Is your DNS acting up?
What service packs have you applied recently to any equipment in the process
(Client, Server, routers/switches etc.).
What has changed in the last few weeks?
Rick Sawtell
MCT, MCSD, MCDBA
"Rob" <Rob@.nul.ca> wrote in message
news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
> Hi,
> I have 3 SQL servers 7, and since yesterday, some of my
> clients started receiving different error messages in the
> middle of doing something like reporting, or editing.
> (they run different applications).
> However when i checked the event logs on all servers there
> is no error message there. all different messages include
> a "General network error" for example one of them is:
> SQL Server Message 10054, Possible network error Write to
> SQL Server Failed. General Network Error. Check your
> documentation. Debugging info: sqlCompile...
> Usually if I reboot their WS, thats temperary fixs their
> problem.
> Does anyone have any idea how to deal with this problem, I
> am monitoring different switches but cannot see any
> problem. Also checked ODBC settings all fine, and we have
> these setting for a couple of years with no problem.
> Thanks in advance for any help-Rob
>|||On 2 of them. The network health is good and I can not see
any problem. As long as I am aware, we havent changed
anything recently and this problem startd since a couple
days ago.
Rob
>--Original Message--
>Are the errors occurring on all 3 SQL Servers or just one
SQL Server?
>How is the health of the network? Are the switches in
good working order?
>How about the NIC's in the SQL Server machines? Is your
DNS acting up?
>What service packs have you applied recently to any
equipment in the process
>(Client, Server, routers/switches etc.).
>What has changed in the last few weeks?
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Rob" <Rob@.nul.ca> wrote in message
>news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
>> Hi,
>> I have 3 SQL servers 7, and since yesterday, some of my
>> clients started receiving different error messages in
the
>> middle of doing something like reporting, or editing.
>> (they run different applications).
>> However when i checked the event logs on all servers
there
>> is no error message there. all different messages
include
>> a "General network error" for example one of them is:
>> SQL Server Message 10054, Possible network error Write
to
>> SQL Server Failed. General Network Error. Check your
>> documentation. Debugging info: sqlCompile...
>> Usually if I reboot their WS, thats temperary fixs their
>> problem.
>> Does anyone have any idea how to deal with this
problem, I
>> am monitoring different switches but cannot see any
>> problem. Also checked ODBC settings all fine, and we
have
>> these setting for a couple of years with no problem.
>> Thanks in advance for any help-Rob
>>
>
>.
>|||Hi Bob,
Thanks for using MSDN Managed Newsgroup!
From your descriptions, I understood that your SQL7 received different
error and General Network Error. Have I understood you? If their is
anything I misunderstood, please feel free to let me know.
First of all, Would you please show me a detailed descriptions of what kind
of error message it will show? Is it possible for you to paste your latest
error log here?
Secondly, based on my scope, "General network error" doesn't mean a network
issue in most cases, it is a very common error that I am afraid we need
more time troubleshooting.
Thirdly, you said it will temperarily fix the problem by rebooting. How
long after rebooting will this kind of error occurs again?
Last but not the least, if it is an urgent one, you can contact Microsoft
Product Support directly to discuss additional support options you may have
available, by contacting us at 1-(800)936-5800 or by choosing one of the
options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Connectivity problem

Hi,
I have 3 SQL servers 7, and since yesterday, some of my
clients started receiving different error messages in the
middle of doing something like reporting, or editing.
(they run different applications).
However when i checked the event logs on all servers there
is no error message there. all different messages include
a "General network error" for example one of them is:
SQL Server Message 10054, Possible network error Write to
SQL Server Failed. General Network Error. Check your
documentation. Debugging info: sqlCompile...
Usually if I reboot their WS, thats temperary fixs their
problem.
Does anyone have any idea how to deal with this problem, I
am monitoring different switches but cannot see any
problem. Also checked ODBC settings all fine, and we have
these setting for a couple of years with no problem.
Thanks in advance for any help-Rob
Are the errors occurring on all 3 SQL Servers or just one SQL Server?
How is the health of the network? Are the switches in good working order?
How about the NIC's in the SQL Server machines? Is your DNS acting up?
What service packs have you applied recently to any equipment in the process
(Client, Server, routers/switches etc.).
What has changed in the last few weeks?
Rick Sawtell
MCT, MCSD, MCDBA
"Rob" <Rob@.nul.ca> wrote in message
news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
> Hi,
> I have 3 SQL servers 7, and since yesterday, some of my
> clients started receiving different error messages in the
> middle of doing something like reporting, or editing.
> (they run different applications).
> However when i checked the event logs on all servers there
> is no error message there. all different messages include
> a "General network error" for example one of them is:
> SQL Server Message 10054, Possible network error Write to
> SQL Server Failed. General Network Error. Check your
> documentation. Debugging info: sqlCompile...
> Usually if I reboot their WS, thats temperary fixs their
> problem.
> Does anyone have any idea how to deal with this problem, I
> am monitoring different switches but cannot see any
> problem. Also checked ODBC settings all fine, and we have
> these setting for a couple of years with no problem.
> Thanks in advance for any help-Rob
>
|||On 2 of them. The network health is good and I can not see
any problem. As long as I am aware, we havent changed
anything recently and this problem startd since a couple
days ago.
Rob

>--Original Message--
>Are the errors occurring on all 3 SQL Servers or just one
SQL Server?
>How is the health of the network? Are the switches in
good working order?
>How about the NIC's in the SQL Server machines? Is your
DNS acting up?
>What service packs have you applied recently to any
equipment in the process[vbcol=seagreen]
>(Client, Server, routers/switches etc.).
>What has changed in the last few weeks?
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>"Rob" <Rob@.nul.ca> wrote in message
>news:d37d01c48ab6$39d66790$a601280a@.phx.gbl...
the[vbcol=seagreen]
there[vbcol=seagreen]
include[vbcol=seagreen]
to[vbcol=seagreen]
problem, I[vbcol=seagreen]
have
>
>.
>
|||Hi Bob,
Thanks for using MSDN Managed Newsgroup!
From your descriptions, I understood that your SQL7 received different
error and General Network Error. Have I understood you? If their is
anything I misunderstood, please feel free to let me know.
First of all, Would you please show me a detailed descriptions of what kind
of error message it will show? Is it possible for you to paste your latest
error log here?
Secondly, based on my scope, "General network error" doesn't mean a network
issue in most cases, it is a very common error that I am afraid we need
more time troubleshooting.
Thirdly, you said it will temperarily fix the problem by rebooting. How
long after rebooting will this kind of error occurs again?
Last but not the least, if it is an urgent one, you can contact Microsoft
Product Support directly to discuss additional support options you may have
available, by contacting us at 1-(800)936-5800 or by choosing one of the
options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Connectivity issues

Hi,

I get the following error message trying to connect locally to sql server 2005 dev edition on xp sp2 machine.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

I have enabled all protocols , the sql browser is running , sql agent and sql database engine not running. All other services like reporting etc are running.

The server is set up to run under local system account. Logging in as administrator, .

This server is installed as default instance . ( I do have sql exp and sql server 200 dev edition installed as named instances)

Beginning to pull my hair out.

Any help would be greatly appreciated.

Regards,

Well, you said the database engine is NOT running? From the looks of things that's what you're trying to connect to...I'll assume a typo maybe? The DB Engine service must be running if you're trying to connect to the SQL engine...

|||

Thank you Chad for the illuminating response.

The problem is precisely that the database engine will not start , I understand that the engine must running order for me to connect to a database , however I have made no mention of trying to connect to a database.

If you have any helpful suggestions I would really appreciate it.

Thank you

Andy

|||

Hi Andy...honestly, everything you wrote in your initial posting was indicating that you were having trouble trying to connect to the server...per your initial post:

"I get the following error message trying to connect locally to sql server 2005 dev edition on xp sp2 machine."

Then, the error message you posted indicates that you are indeed trying to connect to the server from an application:

"An error has occurred while establishing a connection to the server...."

And, it even describes a connection level provider (named pipes)...

So, given your original posting, it seems you are having trouble connecting to the instance, not getting it running. Myself and others would be more than happy to help you debug why you are having trouble getting the engine to start, but we'd need entirely different information.

If you'd like help with why the engine is not able to start, please post any error messages you notice in the application log, sql server error log, and system log related to the SQL Server instance, that's what we'd need to see to help you understand why the engine will not start.

On a final note, bear in mind that folks on the forums are trying to help you, not trying to insult you, and you'll always get more help if you treat myself and others with respect, not by being smart with us. You may notice that I am an administrator of these SQL forums, so please keep posts as civil as possible.

Regards

|||

HI Chad,

My apologies for coming across so curt , having re-read my response it does seem as though I was being rude , not my intention. Perhaps a bit of frustration creeping in, the problem is that my vocabulary and knowledge is lacking and expressing myself in a meaningful way is difficult and perhaps to some one with your knowledge, confusing.

If I am getting an error as described above when trying to get an instance started (i.e. the error is generated when I try and get the engine started) through Management Studio then I assume that error is relevant and that is what I will report on. Having read your second post it is now obvious that the error describes a connection problem rather than an issue regarding the instance running.( which obviously is as result of the engine not running.)

I checked the error log and found a network error which I googled , it seems as though if the via protocol is the culprit , having disabled this protocol the engine started .

The network error in the log:

TDSSNIClient initialization failed with error 0x7e, status code 0x60.

Thank you and my apologies once again.

|||

No worries, I definately understand frustation, we're all quite accustomed to that unfortunately. Glad you were able to get things working,

Regards,

sqlsql