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
>

Connenting to a another SQL Server using Transact-SQL

Hi,

I am finding a solution for how to connect to a SQL server by using Transact-SQL.

Suppose that there are two SQL Servers Call 'A' and 'B'.I just want to connect to the SQL Server 'B' from SQL Server 'A' to Insert data.

How can i do this only using Transact-SQL without using any programming language.

Thanks,

You will have to setup Server B as a linked server on Server A first. Then you will ba able to use frou part notation to access the Server:

Select * from SomeLinkedServerName.DatabaseName.OwnerOrSchema.Objectname

See the linked server topic in the BOL for more information.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Connections made by call to createStatement?

Hi:
I am using the following code to test connections made to an SQL Server 2000
database:
Statement stmt = con.createStatement ();
stmt.executeQuery ("select * from control");
stmt = con.createStatement ();
stmt.executeQuery ("select * from control");
stmt = con.createStatement ();
stmt.executeQuery ("select * from control");
con has previously been set up as a Connection object obtained via a call to
DriverManager.
When I look at the output of sp_who in SQL Query Analyzer, I see that a new
SQL Server process has been created each time that createStatement is called
(actually, not the first time; only one process exists after that call, and
that process appears after the call to DriverManager.getConnection).
I'm trying to avoid the JDBC driver make multiple connections to the SQL
Server for performance reasons; if it really is making a new connection each
time, that would obviously require some time which I don't want to waste.
It's possible I'm misinterpeting the output of Query Analyzer, and that
multiple database processes can be listed that are all using the same actual
connection (this seems possible, as the output of stmt.getConnection()
remains the same each time a stmt object is allocated in the code above).
Can anyone explain this behavior?
Thanks,
Ryan
Add the property 'selectMethod=cursor' to your connection-getting and this
odd behavior of the driver will go away.
Joe
Ryan McFall wrote:

> Hi:
> I am using the following code to test connections made to an SQL Server 2000
> database:
> Statement stmt = con.createStatement ();
> stmt.executeQuery ("select * from control");
> stmt = con.createStatement ();
> stmt.executeQuery ("select * from control");
> stmt = con.createStatement ();
> stmt.executeQuery ("select * from control");
> con has previously been set up as a Connection object obtained via a call to
> DriverManager.
> When I look at the output of sp_who in SQL Query Analyzer, I see that a new
> SQL Server process has been created each time that createStatement is called
> (actually, not the first time; only one process exists after that call, and
> that process appears after the call to DriverManager.getConnection).
> I'm trying to avoid the JDBC driver make multiple connections to the SQL
> Server for performance reasons; if it really is making a new connection each
> time, that would obviously require some time which I don't want to waste.
> It's possible I'm misinterpeting the output of Query Analyzer, and that
> multiple database processes can be listed that are all using the same actual
> connection (this seems possible, as the output of stmt.getConnection()
> remains the same each time a stmt object is allocated in the code above).
> Can anyone explain this behavior?
> Thanks,
> Ryan
>

Connection string Error

Hello I need some help please.

I created a connection string in my web config. I then tried to call it in one of my c# pages but I get a "System.InvalidOperationException: Instance failure" every time I run it.

Please review my code and let me know what I am doing wrong.

Thank you in advance for all the help

Web Config

<

appSettings>

<

addkey="AVConnection"value="Server=Server; Database=DB; User ID=sa; Password=PWD" />

</appSettings>

//////calling connection and running a simple SQL statment///////

protected void Page_Load(object sender, EventArgs e)

{

string ConnApp;

ConnApp = System.Configuration.ConfigurationManager.AppSettings["AVConnection"];

Response.Write(ConnApp);//the string is being passed this far

SqlConnection objConn = new SqlConnection(ConnApp);

SqlCommand sqlCMD = new SqlCommand("Select * FROM Employee", objConn);

objConn.Open();//this is where the error occures

SqlDataReader objRdr = sqlCMD.ExecuteReader();

Repeater1.DataSource = objRdr;

Repeater1.DataBind();

objConn.Close();

objRdr.Close();

}

ERROR PAGE

Line 29: SqlCommand sqlCMD = new SqlCommand("Select * FROM Employee", objConn);
Line 30:
Line 31: objConn.Open();
Line 32: SqlDataReader objRdr = sqlCMD.ExecuteReader();
Line 33: Repeater1.DataSource = objRdr;

[InvalidOperationException: Instance failure.]
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +683775
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
employeelist.Page_Load(Object sender, EventArgs e) in c:\Inetpub\wwwroot\FAUAV\employeelist.aspx.cs:31
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Don't know how much of a difference this might make, but have you tried putting the connection string in the connectionStrings node of web.config? If not you might as well do that as that's why it exists.

Ryan

|||

I tried that but it did not change anything.

Thanks for the advice