Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can i drop table? Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 8:51 AM
Points: 3, Visits: 15

Dear all,
I have an error in my database. I write command that is
'Drop table tb_name'.
When i run that command, I got the error that is:
'Could not find the index entry for RID '1674e85621000300' in index page (1:182), index ID 2, database 'db_name''
So, may i know how can i drop this table.

Thanks for All,
Nay Min
Post #1357850
Posted Wednesday, September 12, 2012 4:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:16 AM
Points: 121, Visits: 1,248
Before applying any solution first run a DBCC CHECKDB command. Check is it reported any error message or not?

SQL Database Recovery Expert
Post #1357878
Posted Wednesday, September 12, 2012 4:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
This is a nonclustered index. You can drop the index first.
Hope you have good backup, in case required.
Post #1357890
Posted Wednesday, September 12, 2012 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Please run the following and post the full and complete output.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Without knowing the extent of the errors, no sensible solution can be suggested.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1357981
Posted Wednesday, September 12, 2012 10:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 8:51 AM
Points: 3, Visits: 15
Thanks for your reply:
When checking Database with
DBCC CHECKDB()......
I got many error lines. First few lines are:

"Msg 8952, Level 16, State 1, Line 1
Table error: Database 'FOR11213', index 'sysobjects.ncsysobjects' (ID 1) (index ID 2). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:186:32) with values (name = 'NSD34949300' and uid = 1 and id = 1652942452) points to the data row identified by ().
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysobjects' (object ID 1).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:28568:0) identified by (RID = (1:28568:0) id = 1652942452 and colid = 17 and number = 0) has index values (id = 1652942452 and name = 'Brand_ID' and number = 0 and id = 1652942452 and colid = 17 and number = 0).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:28568:1) identified by (RID = (1:28568:1) id = 1652942452 and colid = 18 and number = 0) has index values (id = 1652942452 and name = 'Order_Convert_Price' and number = 0 and id = 1652942452 and colid = 18 and number = 0).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:28568:2) identified by (RID = (1:28568:2) id = 1652942452 and colid = 19 and number = 0) has index values (id = 1652942452 and name = 'Sr_No' and number = 0 and id = 1652942452 and colid = 19 and number = 0).
CHECKDB found 0 allocation errors and 3 consistency errors in table 'syscolumns' (object ID 3)."

Thanks and bet regards
NayMin
Post #1358326
Posted Thursday, September 13, 2012 1:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
I asked for the full and complete output. I asked for that for a reason, one cannot make sensible recommendations about fixing a DB from partial output from CheckDB

Now, please post the full and complete, unedited output. Either in the text of the post or attach it as a text file attachment.

Do you have a clean backup? One without the corruption?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1358360
Posted Thursday, September 13, 2012 9:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 8:51 AM
Points: 3, Visits: 15
Hi, This is complete error:
" Msg 8952, Level 16, State 1, Line 1
Table error: Database 'FOR11213', index 'sysobjects.ncsysobjects' (ID 1) (index ID 2). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:186:32) with values (name = 'NSD34949300' and uid = 1 and id = 1652942452) points to the data row identified by ().
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysobjects' (object ID 1).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:4312:0) identified by (RID = (1:4312:0) id = 1652942452 and colid = 17 and number = 0) has index values (id = 1652942452 and name = 'Brand_ID' and number = 0 and id = 1652942452 and colid = 17 and number = 0).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:4312:1) identified by (RID = (1:4312:1) id = 1652942452 and colid = 18 and number = 0) has index values (id = 1652942452 and name = 'Order_Convert_Price' and number = 0 and id = 1652942452 and colid = 18 and number = 0).
Msg 8951, Level 16, State 1, Line 1
Table error: Table 'syscolumns' (ID 3). Missing or invalid key in index 'ncsyscolumns' (ID 2) for the row:
Msg 8955, Level 16, State 1, Line 1
Data row (1:4312:2) identified by (RID = (1:4312:2) id = 1652942452 and colid = 19 and number = 0) has index values (id = 1652942452 and name = 'Sr_No' and number = 0 and id = 1652942452 and colid = 19 and number = 0).
CHECKDB found 0 allocation errors and 3 consistency errors in table 'syscolumns' (object ID 3).
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Order_Convert_Price'. The column ID 18 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Sr_No'. The column ID 19 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Location_ID'. The column ID 2 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Stock_ID'. The column ID 3 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'UM_ID'. The column ID 4 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Invoice_Quantity'. The column ID 5 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Selling_Price'. The column ID 6 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Convert_Price'. The column ID 7 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Stocking_Quantity'. The column ID 8 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Invoice_Date'. The column ID 9 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Order_ID'. The column ID 10 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Delivering_Qty'. The column ID 11 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Delivering_S_Qty'. The column ID 12 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Stocking_Price'. The column ID 13 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Expired_Date'. The column ID 14 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Item_Discount'. The column ID 15 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Serial_No'. The column ID 16 is not valid for this table. The valid range is from 1 to 1.
Msg 8932, Level 16, State 2, Line 1
Table error: Object ID 1652942452, index ID 0, column 'Brand_ID'. The column ID 17 is not valid for this table. The valid range is from 1 to 1.
CHECKDB found 0 allocation errors and 18 consistency errors in table 'NSS35427410' (object ID 1652942452).
CHECKDB found 0 allocation errors and 22 consistency errors in database 'FOR11213'. "


I have no clean backup. I can run 'SELECT * FORM NSD34949300' but I can't drop that table. There is no data in this table. Now, When I run 'Drop Table NSD34949300',error appear that:
'Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'NSD34949300', because it does not exist in the system catalog.'

Thanks and best regards.
NayMinn
Post #1359026
Posted Friday, September 14, 2012 2:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Not repairable. There's damage to the system tables.

Script all objects, export all data and recreate the database. Then set up a backup strategy.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1359115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse