We are getting index problems on 2 of our tables. The corruptions are intermittent and only started occurring on 22 Aug. We have been told by our customer that no changes have occurred on their systems recently, and I know that I haven't put anything new into the application either.
Our customer is running (@@Version)
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Some Questions please.
1. Is there anyway that the application could be causing the corruption.
I have seen this from Paul Randal
"Not possible ever for an app bug to cause database corruption - no matter how hard it tries.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine"
Please forgive any unintended insult, but does Paul actually work for Microsoft on this product? I have seen some of his replies and he seems highly, highly conversant with the product and given my limited knowledge of SQL and DB in general I agree that the application should not be able to cause this problem. I need to be able to demonstrate this to both my boss and our customer and a statement like the above from Paul would go a long way to providing the proof I need.
2. Why would the problem suddenly start on 22 Aug and happen regularly thereafter, given that there is supposedly no change to SQL or the application.
We have not been able to replicate the problem in the test region, even though we are able to process the production files there. The customer is reluctant to start full SQL logging given the performance hit.
We have a program which accepts a file of SQL statements and processes them. The input file can be up to about 50Mb and contain many thousands of statements. The structure of the file is this and the program just parses each statement between the <PGMBCL> and sends it to SQL
<PGMBCL>
DELETE FROM HRQUAARI.ZY8C WHERE NUDOSS = +714307848
<PGMBCL>
INSERT INTO HRQUAARI.ZY8C (NUDOSS,NULIGN,SOCDOS,FILLE1,PERTRT,FILLE2,NUM
TRT,NUMBUL,NUMCUM,CUMANN,CUMPER,DATCUM,MONCUM,IDCY00) VALUES ( +714307848, +0000
01, 'OCM', '20', '0506', ' ', '0', '01', '178', '01', '10', '1753-01-01', +00000
008072.2200, ' ')
etc.. another 36 records ..etc
<PGMBCL>
DELETE FROM HRQUAARI.ZYTD12 WHERE NUDOSS = +714307848 AND CDINFO = '8C'
<PGMBCL>
INSERT INTO HRQUAARI.ZYTD12 (NUDOSS,CDINFO,TIMJIF,TEVERO,NOMBRE,NULMAX,NUC
MAX) VALUES ( +714307848, '8C', '2005-09-23-14.36.05', '0', +00037, +000037, +00
0037)
and the DBCC CHECKTABLE ('HRUKPARI.ZY8c') results are :-
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1462733:47) with values (NUDOSS = 712430859 and NULIGN = 48) points to the data row identified by (RID = (1:1453030:105)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1468453:32) with values (NUDOSS = 712430858 and NULIGN = 50) points to the data row identified by (RID = (1:1453030:104)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1064449:83) with values (NUDOSS = 712430858 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '02' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:104)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1463770:32) with values (NUDOSS = 712430859 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '04' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:105)).
DBCC results for 'HRUKPARI.ZY8C'.
There are 352118 rows in 2656 pages for object 'HRUKPARI.ZY8C'.
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'HRUKPARI.ZY8C' (object ID 112771509).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (HRUKPARI.HRUKPARI.ZY8C ).
I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo'); SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
Hi, I run a nightly dbcc checktable on order_header and today I see this error occurs. Table error: Table 'ORDER_HEADER' (ID 203147769). Missing or invalid key in index 'PK_ORDER_HEADER' (ID 4) for the row: Server: Msg 8955, Level 16, State 1, Line 1 1. I have no idea what caused it? 2. How do I fixed this online as the application is 24/7? 3. I did try to dbcc reindex but it failed reporting that can't create index because of duplicate in primary index key????
We have such problems: DBCC results for 'TRAMES'. Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:34) identified by (RID = (1:3893993:34) ) has index values (PO_ID = 80123). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:48) identified by (RID = (1:3893993:48) ) has index values (PO_ID = 80095). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:79) identified by (RID = (1:3893993:79) ) has index values (PO_ID = 80123). Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:3893993:92) identified by (RID = (1:3893993:92) ) has index values (PO_ID = 80095). There are 1703901 rows in 18180 pages for object 'TRAMES'. CHECKDB found 0 allocation errors and 4 consistency errors in table 'TRAMES' (object ID 1573580644).
And really I don't know what can I do to prevent such problems. I know how to fix it with a dbreindex, but it blocks the production during several minutes, and we have to restart the whole plant...
we are using SQL 2000 standard edition 8.00.2039 (with SP4)
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools] FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId) Or CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
hi lets say i have table student(id,name) id =pk table course(cno,cname)cn=pk now iam a fresh graduate as i learned from uni if i want to get the couses that each student took i would make a table called studentcouse(id,cno) and put the two of them pk now iam working and at work they told me to do so:studentcouse(studentcouse_serial,id,cno) studentcouse_serial=pk .but i told them that dublicate filed may occur amd they told me that we have a function that will remove dublicate. so iam asking u if who is right me or them and if u can tell yr comments thanx a lot
Hi, Can anybody of u guyz help me out of this. I transferred my tables(about 250) to another server using DTS. But all my keys got dropped. I tried using Replication and still the destination is without my keys. What i need to do get my keys? Do i need to take a script of my keys and execute it there in destination server? If so, everytime i need to do that? Please help me out in these problem.. I really appreciate any help amidst your precious schedule..
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended." can soemone explain to me why i have this kind of error? i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no! all the values from of application_input will be stored also in approve_overtime. sometimes the datas can be stored.sometimes it cannot and produces an error!
Hi, I am currently playing with Sql Express 2005, and I am wondering if there is any way to create secondary keys on a table? I know you can create compound Primary Keys (not sure that is the correct terminology), and Foregin Keys. However, I am unsure about secondary keys ( compound or otherwise). Could someone tell me if this is possible. I am looking at migrating from an Acucobol Vision file system, which makes heavy use of secondary keys. Cheers
i have some confusions with keys and indexes.. plz let me know whether the following are correct..- Every Primary Key is a Clustered Index- A Primary Key cannot exist without a Clustered Index- Every Unique Key is a Non-Clustered Index - Non-Clustered Index is the DEFAULT Index- A table can have only 1 Primary key- A table can have only 1 Clustered Index- A table can have any number of Unique Keys- A table can have any number of Non-Clustered Indexes
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered unique indexes. Is there a configuration setting I can use to make them be created as clustered unique indexes?
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.
Could someone enlighten me as to the advantage of using the foreign key tab when in table design mode in the Enterprise Manager. Does it have any advantages ?? Is it necessary ??
I haven't tried, but does anyone know if its possible to a have a foreign key for two tables when the tables reside in different databases (on the same server)?
l'm trying to do inserts on tables with foreign keys and they keep crashing. Can somebody please help.Whats the best way of populating data that has foreign keys?
I have recently come across a DB Schema without about 300 tables, none of which have any keys associated with them. What I did notice however is the schema has 3000 or so SProcs which is handling most of the validations.
Does anyone know of any advantages/disadvantages to this approach? I have always been around DB Engineers who have stressed upon having keys for Table level integrity.
I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:
'table_name' table - Unable to create index 'PK_tablename'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated. i.e. key1 key2 200410 12345 200410 12346 200410 12588 etc for all 200410 there can not be a repeat of any value of key 2
is there a way to have this dual key in MSSQL :confused:
I am creating a series of foreign keys in my new database, and so far everything is going fine. The company I work for never uses them, and I am working on my own stuff on my own time now.
I come across one table that I do want one field to allow zeroes, but when its set to a value, to exist in the other table.
To be more specific. I have a PO type table, which has an EmployeeID of the person who made the PO, and another field to store who received the PO when the order arrives. I want that 2nd Employee field to be zero until it's received.
I created an employee of zero, to allow the foreign key to be created. But all the other tables that have foreign keys to my Employee table I would prefer to not allow zeroes. So I changed them to use a check of (employeeid > 0).
Is it possible to have a foreign key say that I want the value from the Field in Table A to exist in table B, or to be zero? Or would it just be easier to leave off the foreign key in this one case?
