Incorrect syntax when there appears to be no syntax errors.

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'

Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @.strProjectName, " _
& "txtProjectDescription = @.txtProjectDescription, " _
& "strProjectState = @.strProjectState, " _
& "intEstDuration = @.intEstDuration, " _
& "dtmCreationDate = @.dtmCreationDate, " _
& "strCreatedBy = @.strCreatedBy, " _
& "strProjectLead = @.strProjectLead, " _
& "dtmEstCompletionDate = @.dtmEstCompletionDate, " _
& "WHERE intProjectID = @.intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@.strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@.strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@.txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@.intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@.intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@.dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@.strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@.strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@.dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@.dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@.intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()

End Sub

Thankyou in advance.> cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))

why wouldintProjectID be an NChar? or is that just misleading?|||You have an extra comma.

"dtmEstCompletionDate = @.dtmEstCompletionDate, " _
"WHERE ... "

that would result in:

dtmEstCompletionDate = '1/1/2003', WHERE

there's an extra comma before the WHERE clause. That is causing the syntax error.

Cheers
Ken|||Good one!sql

Incorrect syntax near the keyword 'Close'

When I created a SQL Server database by running a script, it gave me a
few errors like the following:
Incorrect syntax near the keyword 'KEY'.
Incorrect syntax near the keyword 'Close'.
Incorrect syntax near the keyword 'Open'.
Is this because those words (Key, CLose and Open) are reserved words ?
Thanks.We would definitely need to view the script in order to help you out here .
Could you post the script ?
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||Hi
Look at the subject "Reserved Keywords" in Books online all the words you
list are keywords. It is possible to use delimited identifiers if you want
to keep the keyword as an identifier see the topics
John
"Using Reserved Keywords" and "Delimited Identifiers" in Books online.
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||If you use keywords( documented in Books on line) as the names of ANY
objects in SQL you must brace them if
create table [OPEN]
([Key] int not null)
It is a good idea NOT to use reserve words if you can avoid it, because
you'll be forgetting to use the brackets and re-doing code over and
over(kind of annoying.)
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
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
enclose identifiers in double quotes. This alternative to square brackets
is the ANSI-standard method. The best practice is to avoid reserved words,
though.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "OPEN"
("Key" int NOT NULL)
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||CREATE TABLE tblA (
Price varchar(50) NULL,
close varchar(50) NULL,
group1 varchar(50) NULL,
Cost varchar(50) NULL
)
go
CREATE TABLE tblB (
Product varchar(50) NULL,
open datetime NULL,
close datetime NULL
)
go
CREATE TABLE tblC (
key varchar(50) NULL,
First_name varchar(50) NULL
)
go
When I replaced "Close" to "Close1", "Open" to "Open1" and "key" to
"key1", the error did not appear anymore.
Thanks.
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<uEm9gM1oEHA.1900@.TK2MSFTNGP10.phx.gbl>...
> We would definitely need to view the script in order to help you out here .
> Could you post the script ?
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.|||Thank you.
If I use square brackets or double quotes on the colum name, do I access
that column with the square brackets or double quotes also ?
For example:
create table tblA ( [open] varchar(50) )
When I want to select column [open], do I do the following sql statement:
select open from tblA
OR
select [open] from tblA ?
create table tblA ( "open" varchar(50) )
When I want to select column "open", do I do the following sql statement:
select "open" from tblA
OR
select "open" from tblA ?
Thank you very much.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
> enclose identifiers in double quotes. This alternative to square brackets
> is the ANSI-standard method. The best practice is to avoid reserved
words,
> though.
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE "OPEN"
> ("Key" int NOT NULL)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.
>|||Enclosures are required when you use a reserved word but it doesn't matter
whether you use square brackets or double quotes. You can mix both.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:eHXDZNDpEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> If I use square brackets or double quotes on the colum name, do I access
> that column with the square brackets or double quotes also ?
> For example:
> create table tblA ( [open] varchar(50) )
> When I want to select column [open], do I do the following sql statement:
> select open from tblA
> OR
> select [open] from tblA ?
> create table tblA ( "open" varchar(50) )
> When I want to select column "open", do I do the following sql statement:
> select "open" from tblA
> OR
> select "open" from tblA ?
> Thank you very much.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
>> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
>> enclose identifiers in double quotes. This alternative to square
>> brackets
>> is the ANSI-standard method. The best practice is to avoid reserved
> words,
>> though.
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TABLE "OPEN"
>> ("Key" int NOT NULL)
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "fniles" <fiefieniles@.yahoo.com> wrote in message
>> news:2067fd92.0409251452.60e065d7@.posting.google.com...
>> > When I created a SQL Server database by running a script, it gave me a
>> > few errors like the following:
>> >
>> > Incorrect syntax near the keyword 'KEY'.
>> > Incorrect syntax near the keyword 'Close'.
>> > Incorrect syntax near the keyword 'Open'.
>> >
>> > Is this because those words (Key, CLose and Open) are reserved words ?
>> >
>> > Thanks.
>>
>

Incorrect syntax near the keyword 'Close'

When I created a SQL Server database by running a script, it gave me a
few errors like the following:
Incorrect syntax near the keyword 'KEY'.
Incorrect syntax near the keyword 'Close'.
Incorrect syntax near the keyword 'Open'.
Is this because those words (Key, CLose and Open) are reserved words ?
Thanks.
We would definitely need to view the script in order to help you out here .
Could you post the script ?
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||Hi
Look at the subject "Reserved Keywords" in Books online all the words you
list are keywords. It is possible to use delimited identifiers if you want
to keep the keyword as an identifier see the topics
John
"Using Reserved Keywords" and "Delimited Identifiers" in Books online.
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||If you use keywords( documented in Books on line) as the names of ANY
objects in SQL you must brace them if
create table [OPEN]
([Key] int not null)
It is a good idea NOT to use reserve words if you can avoid it, because
you'll be forgetting to use the brackets and re-doing code over and
over(kind of annoying.)
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
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
enclose identifiers in double quotes. This alternative to square brackets
is the ANSI-standard method. The best practice is to avoid reserved words,
though.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "OPEN"
("Key" int NOT NULL)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||Thank you.
If I use square brackets or double quotes on the colum name, do I access
that column with the square brackets or double quotes also ?
For example:
create table tblA ( [open] varchar(50) )
When I want to select column [open], do I do the following sql statement:
select open from tblA
OR
select [open] from tblA ?
create table tblA ( "open" varchar(50) )
When I want to select column "open", do I do the following sql statement:
select "open" from tblA
OR
select "open" from tblA ?
Thank you very much.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
> enclose identifiers in double quotes. This alternative to square brackets
> is the ANSI-standard method. The best practice is to avoid reserved
words,
> though.
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE "OPEN"
> ("Key" int NOT NULL)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.c om...
>
|||Enclosures are required when you use a reserved word but it doesn't matter
whether you use square brackets or double quotes. You can mix both.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:eHXDZNDpEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> If I use square brackets or double quotes on the colum name, do I access
> that column with the square brackets or double quotes also ?
> For example:
> create table tblA ( [open] varchar(50) )
> When I want to select column [open], do I do the following sql statement:
> select open from tblA
> OR
> select [open] from tblA ?
> create table tblA ( "open" varchar(50) )
> When I want to select column "open", do I do the following sql statement:
> select "open" from tblA
> OR
> select "open" from tblA ?
> Thank you very much.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> words,
>

inconsistency errors and how to fix them?

One of the db's I support is prone to throwing up inconsistency errors from time to time. The latest problem seems to relate to indexes.

Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 645577338, Index ID 3. Keys out of order on page (1:286818), slots 308 and 309.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 645577338, Index ID 3. Keys out of order on page (1:286826), slots 308 and 309.
DBCC results for 'OrderItem'.
There are 9973753 rows in 67394 pages for object 'OrderItem'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'OrderItem' (object ID 645577338).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (Iceland.dbo.OrderItem ).

I could run a DBCC checktable with the options that *might* fix the above. I am also thinking about doing an index rebuild (currently a nightly index defrag is done but no schedule for an index rebuild is in place). Another table is involved. Both table are reasonably large (one is 7 million rows and the other is nearly 10 million rows). These tables are involved in a chain of many to many relationships. I don't know if it's a factor but the chain is circular. However, this doesn't seem to cause problem in other dbs that have the same design.

What would be the recommended approach for:-

a) fixing the current db inconsistencies?
b) finding the underlying cause of the problem to stop it from happening again?

Thank in advance,

CliveMicrosoft Knowledge Base Article - 822747

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you allI'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:
> Is there any possibility to correct such type of allocation problems without
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check for
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
>> Is there any possibility to correct such type of allocation problems
>> without
>> data loss?
>> After correction with ALLOW_DATA_LOSS option, is there any tool to check
>> for
>> referential integrity to discover witch records on witch tables
>> disppeared
>> with deallocations?
>> Thanks you all|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated by
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||I obtained an unexpected result from the execution of CHECKDB with
REPAIR_REBUILD option.
0 allocation errors and 319 consistency errors found
All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
What did it happen? The 5 allocation error disappeared?
Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
"Ryan Stonecipher [MSFT]" wrote:
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > I'm sorry, I searched in the newsgroup and I found in many cases there is
> > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > it).
> > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > takes
> > hours...)
> >
> > "Argo" wrote:
> >
> >> Is there any possibility to correct such type of allocation problems
> >> without
> >> data loss?
> >> After correction with ALLOW_DATA_LOSS option, is there any tool to check
> >> for
> >> referential integrity to discover witch records on witch tables
> >> disppeared
> >> with deallocations?
> >> Thanks you all
> >>
>
>|||Hi
After a DB has gone corrupt, and having 'fixed' it, I always DTS all the
data out into another DB. I don't trust the original DB anymore. Use the new
DB as the working one and the 'fixed' one as a reference in case you are
worried about data loss.
The problem with SAP is that it does not use FK constraints, so knowing if
all the data is there after a fix, is difficult.
If you have a backup from before the corruption, with all the logs to the
present, do a restore. Use this to verify how much data you have lost by
comparing it to your fixed DB.
At the same time, tell Finance that they have to sign-off on the reports
they run after you fix the DB. Then you have covered your ass, in case they
come to you next year and say "you know, you lost us more data than what we
though" and blame you for the company going bust.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||Sometimes a single error can 'hide' other errors by preventing a particular
set of checks running. In this case it seems like one of the previous
rebuild operations you performed fixed an allocation error that was
preventing the non-clustered index cross-checks from running (this check is
what found the 8952 and 8956 errors). This is completely expected behavior.
Without studying the initial list of errors from CHECKDB as well as your
database in the corrupted state, I can't tell the exact sequence of events
but what I've said seems the most likely (and you're not going to get a
better explanation from anyone else I'm afraid)
Now that you've run all these repairs, your database's inherent business
logic is likely to be broken unless you've taken pains to keep track of
exactly what's been done by repair.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> I obtained an unexpected result from the execution of CHECKDB with
> REPAIR_REBUILD option.
> 0 allocation errors and 319 consistency errors found
> All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> What did it happen? The 5 allocation error disappeared?
> Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
>
>
> "Ryan Stonecipher [MSFT]" wrote:
> > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > problem. These are allocation errors, and will likely not be affected
by a
> > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
you
> > have one available?
> >
> > --
> > Ryan Stonecipher
> > Microsoft Sql Server Storage Engine, DBCC
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Argo" <falk62@.yahoo.com> wrote in message
> > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > I'm sorry, I searched in the newsgroup and I found in many cases there
is
> > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > it).
> > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > takes
> > > hours...)
> > >
> > > "Argo" wrote:
> > >
> > >> Is there any possibility to correct such type of allocation problems
> > >> without
> > >> data loss?
> > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
check
> > >> for
> > >> referential integrity to discover witch records on witch tables
> > >> disppeared
> > >> with deallocations?
> > >> Thanks you all
> > >>
> >
> >
> >|||In a previous post I specified the initial 5 allocation errors, if it can be
useful for you...
The CHECKDB with repair_fast option fixed 1 more 8952/8956 error. Now I
launched a new CHECKDB (without any option) that will end in about 9 hours
from now.
I didn't understand the last part of your post. What do you mean when you
say: "...your database's inherent business logic is likely to be broken
unless you've taken pains to keep track of exactly what's been done by
repair..."?
I have printed all the subsequent errors found and fixed by the DBCC tool.
I kept track of the order of actions I executed so I can reapply them in the
production system step by step.
Is business logic most likely lost anyway? In this case I can't rely on this
solution because it's hard to verify SAP's business logic (see Mike Epprecht
post)
Thanks
Andrea
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>|||Finally the checkdb is OK. There are no errors. The complete sequence of
actions I executed is:
1) initial checkdb (5 allocation errors 89 consistency error)
(repair_allow_data_loss minimum required)
2) checktable (BSIS,repair_rebuild) 55 consistency error fixed
3) checktable (BSE_CLR,repair_rebuild) 28 consistency error fixed
4) checktable (BSAD,repair_rebuild) 6 consistency error fixed
5) checkdb with repair_rebuild 319 new consistency error fixed
6) checkdb 1 more consistency error found (repair_fast minimun required)
7) checkdb with repair_fast (1 consistency error fixed)
8) final checkdb (NO ERRORS)
As you can see I never used more than repair_rebuild level checks.
May I be sure I didn't lose any data?
Why you say that "...Now that you've run all these repairs, your database's
inherent business logic is likely to be broken ..."' What do you mean?
Thank you Paul
"Paul S Randal [MS]" wrote:
> Sometimes a single error can 'hide' other errors by preventing a particular
> set of checks running. In this case it seems like one of the previous
> rebuild operations you performed fixed an allocation error that was
> preventing the non-clustered index cross-checks from running (this check is
> what found the 8952 and 8956 errors). This is completely expected behavior.
> Without studying the initial list of errors from CHECKDB as well as your
> database in the corrupted state, I can't tell the exact sequence of events
> but what I've said seems the most likely (and you're not going to get a
> better explanation from anyone else I'm afraid)
> Now that you've run all these repairs, your database's inherent business
> logic is likely to be broken unless you've taken pains to keep track of
> exactly what's been done by repair.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:875CCF2F-8169-43D3-9D57-85520FEA261C@.microsoft.com...
> > I obtained an unexpected result from the execution of CHECKDB with
> > REPAIR_REBUILD option.
> > 0 allocation errors and 319 consistency errors found
> > All the 319 consistency errors fixed. They were all Errors 8952 and 8956.
> > What did it happen? The 5 allocation error disappeared?
> >
> > Now I'm executing a simple CHEKDB with NO_INFOMSGS,ALL_ERRORMSGS
> >
> >
> >
> >
> > "Ryan Stonecipher [MSFT]" wrote:
> >
> > > If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
> to
> > > fix these errors, then a REPAIR_REBUILD will not completely remove the
> > > problem. These are allocation errors, and will likely not be affected
> by a
> > > REPAIR_REBUILD. Your best bet is always to restore from a backup. Do
> you
> > > have one available?
> > >
> > > --
> > > Ryan Stonecipher
> > > Microsoft Sql Server Storage Engine, DBCC
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Argo" <falk62@.yahoo.com> wrote in message
> > > news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
> > > > I'm sorry, I searched in the newsgroup and I found in many cases there
> is
> > > > nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> > > > it).
> > > > For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> > > > takes
> > > > hours...)
> > > >
> > > > "Argo" wrote:
> > > >
> > > >> Is there any possibility to correct such type of allocation problems
> > > >> without
> > > >> data loss?
> > > >> After correction with ALLOW_DATA_LOSS option, is there any tool to
> check
> > > >> for
> > > >> referential integrity to discover witch records on witch tables
> > > >> disppeared
> > > >> with deallocations?
> > > >> Thanks you all
> > > >>
> > >
> > >
> > >
>
>

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you all
I'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:

> Is there any possibility to correct such type of allocation problems without
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check for
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>
|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...[vbcol=seagreen]
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
is[vbcol=seagreen]
check
>
|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated by
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>
|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>

Inconsistencies Errors 8904 8913 8928 8906

Is there any possibility to correct such type of allocation problems without
data loss?
After correction with ALLOW_DATA_LOSS option, is there any tool to check for
referential integrity to discover witch records on witch tables disppeared
with deallocations?
Thanks you allI'm sorry, I searched in the newsgroup and I found in many cases there is
nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try it).
For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it takes
hours...)
"Argo" wrote:

> Is there any possibility to correct such type of allocation problems witho
ut
> data loss?
> After correction with ALLOW_DATA_LOSS option, is there any tool to check f
or
> referential integrity to discover witch records on witch tables disppeared
> with deallocations?
> Thanks you all
>|||If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required to
fix these errors, then a REPAIR_REBUILD will not completely remove the
problem. These are allocation errors, and will likely not be affected by a
REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
have one available?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Argo" <falk62@.yahoo.com> wrote in message
news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...[vbcol=seagreen]
> I'm sorry, I searched in the newsgroup and I found in many cases there is
> nothing to do even with the ALLOW_DATA_LOSS option (I still didn't try
> it).
> For the moment I'm waiting for a CHECKTABLE REPAIR_REBUILD to end (it
> takes
> hours...)
> "Argo" wrote:
>|||Regarding your question about checking constraints, the only tool in SQL
Server is DBCC CHECKCONSTRAINTS which will validate FOREIGN KEY and CHECK
constraints. There's nothing that will validate referential integrity
constraints.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Stonecipher [MSFT]" <ryanston@.microsoft.com> wrote in message
news:OCSCHmuCFHA.2608@.TK2MSFTNGP10.phx.gbl...
> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required
to
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
is[vbcol=seagreen]
check[vbcol=seagreen]
>|||Now I executed CHECKTABLE repair_rebuild on all the three tables indicated b
y
the checkdb (they repaired all the inconsistencies). It has taken 10 hours
because the tables are very big. Now I started a CHECKDB repair_rebuild and
I
would like to show you the results without all the inconsistencies fixed by
the checktable commands.
The good backup should be of january 14 because the HW problem occurred on
15 but everything looked OK up to these days. (It is a SAP R/3 Production
System).
I think the possibility to reapply all the logs since then should be
avoided. In this period we had a tremendous activity on this system.(closing
of 2004 accounting)
Isn't it?
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required t
o
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>|||I analysed the CHECKDB log. There are 89 consistency errors that have been
fixed with the subsequent CHECKTABLE repair_rebuild commands. But there are
these 5 allocation errors:
Server: Msg 8904, Level 16, State 1, Line 1
Extent (3:1488776) in database ID 7 is allocated by more than one allocation
object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'SGAM' and at least one other object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 0, index ID 0: Page (3:1488782) could not be processed. See other
errors for details.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (3:1488776) is allocated to 'BSAD' and at least one other object.
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:1488782) in database ID 7 is allocated in the SGAM (3:1022465) and
PFS (3:1488192), but was not allocated in any IAM. PFS flags 'IAM_PG
MIXED_EXT ALLOCATED 0_PCT_FULL'.
they all relate to just one extent and one page as I can see. Does the
allow_data_loss level eliminates the problem? In this case I have lost 9
pages of data (8+1=72K)?
Thank you all for your help
p.s. I'm testing the checkconstraints behaviour
"Ryan Stonecipher [MSFT]" wrote:

> If DBCC reports that REPAIR_ALLOW_DATA_LOSS is the repair level required t
o
> fix these errors, then a REPAIR_REBUILD will not completely remove the
> problem. These are allocation errors, and will likely not be affected by
a
> REPAIR_REBUILD. Your best bet is always to restore from a backup. Do you
> have one available?
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Argo" <falk62@.yahoo.com> wrote in message
> news:9BE9595C-FD19-4557-969D-F46B56985D36@.microsoft.com...
>
>