Fixing “Extra or Invalid Key...” Error Message

The information related to records in MS SQL Server database table is either saved in clustered or non-clustered index. In clustered index, the data rows in the table are stored with their unique key value. However, in non-clustered index, every data row in the SQL Server table consists of exactly one matching index row. The problem mainly arises when the non-clustered index consists of an index row, which does not match any data row in the table. This situation occurs when the database is corrupted or damaged. In such situations, the user receives a table error message that does not allows him/her to access the data saved in the table. To access the table data post SQL database corruption, the user needs to restore the data from an updated backup. However, if no backup is available or backup falls short of restoring the required data, the user needs to repair the database using SQL Repair application. Consider the below error message that appears when you try to access the data saved in your SQL Server table: “Table error: Database 'DBNAME', index 'OBJNAME.INDNAME' (ID O_ID) (index ID I_ID). Extra or invalid key for the keys” After the above error message, the records stored in the database table become inaccessible. Furthermore, no operations (add or update) can be performed on that table. Cause The above table error message appears when the index row in the non-clustered index does not match any data row in the table that you are accessing. This scenario occurs when the database is logically or physically damaged. Resolution The resolution for the above error message is repair of corrupted MS SQL Server database. To systematically do so, you need to follow the below measures: 1.Change the damaged physical system component with a brand new component. 2.Run DBCC CHECKDB command with repair clause in case of logical corruption of database.

A Story of the Deleted Transaction Log

I was lying in bed one Saturday night and my phone rang. It was one of our team leads on the other end. The conversation began something like this. "Hey, this client ran out of disk space so we deleted the transaction log for the database as it was over 100GB". Well I wasn't quite asleep but I certainly wasn't awake and my initial thought was this guy thinks he is having a little fun with me. Before my mind could fully envision the animated balled up pieces of paper dematerializing as they were thrown towards the recycle bin I heard the funniest thing of the night come out of the leads mouth, "Do you think that is related to the application not functioning?"

After rubbing my eyes and placing the jack back under the bed that I often use to get my jaw back into place, I asked if the LDF file was in the Recycle Bin. I am sure you know that the answer to that question was "No", mainly because the file is too big for most bins and secondly because nothing is that easy. Obviously the next question was "When was the last backup?" Wouldn't you know there wasn't one, go figure!

To fill you in on a little detail, the company I work for develops software. Our support model includes support of the applications, and we often get involved with database issues, but we do not own or maintain databases. The client had called the support line and stated they were out of space and asked if we could help them. This particular client does not have a DBA on staff, so our technicians were trying to help them. Certainly the wrong route was taken but at this point it was too late to look back. I am the sole DBA on the support side of the company, and since this was now a DBA issue that was caused by our technicians, I had to come up with a solution. The solution I came up with was the best for this particular issue based upon all the factors, specifically no backups.

After getting over the ridiculousness of the issue I began researching how to resolve the problem. I ran into a few hints in BOL and then other hints on the Internet. After all the research, I determined the best issue would be to use the DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. BOL stated this option could repair a transaction log. Well what could be more broken about a transaction log than not having one at all?

Once I determined I would go down this path the first problem was that the db would not go online, not even in emergency mode. So what I did was stop the SQL Server service and then I renamed the MDF file. Let's say the database name was DbLive and I renamed the MDF from DbLive.mdf to DbBad.mdf. I then started the SQL Server service and created a new DbLive db with a corresponding DbLive_log.ldf file. I then stopped the SQL service again and deleted the DbLive.mdf file. I then renamed DbBad.mdf back to DbLive.mdf. I then started the SQL service again.

You can imagine that these steps didn't fix the problem. The db did not come on line, but at least the db now had a transaction log, albeit one from a different database. But as far as SQL server was concerned the log was there now. It was just in a jacked-up state (yes that is a technical term).

The next thing I did was run ALTER DATABASE DbLive SET EMERGENCY. This put the db in emergency mode and allowed SQL server to work with the db at an emergency level. Next I ran "sp_dboption 'DbLive', 'single user', 'true' ". This set the db to single user mode preparing it for the DBCC that I would run next. Finally I ran "DBCC CHECKDB ('DbLive', REPAIR_ALLOW_DATA_LOSS)". After about an hour the db was ONLINE!

I took the db out of single user mode by running "sp_dboption 'DbLive', 'single user', 'false'" I then ran a few queries against the database and all was fine. I am not totally sure what data loss there was, if any. Since the MDF was intact and the SQL service was taken off line and then the LDF file was deleted all data should have been committed to the MDF. So I believe there was no data loss. This happened several months ago and there has not been any reported db or application anomalies so I must assume all data was present.

Oh and yes, I slapped a maintenance plan on that system and sent the backups to an obscure folder. If they run out of space again it will probably be due to having too many backups! What I meant to say was that I explained to the client how important backups are.

Script:

/*Stop SQL service, rename DbLive.mdf to DbBad.mdf.started SQL service, created fake DbLive db (with log etc)Stopped SQL serviceDeleted DbLIve.mdf Renamed DbBad.MDF to DbLive.MDFStarted SQL service.Ran following script:*/ALTER DATABASE DbLive SET EMERGENCYsp_dboption 'DbLive', 'single user', 'true' DBCC CHECKDB ('DbLive', REPAIR_ALLOW_DATA_LOSS)sp_dboption 'DbLive', 'single user', 'false' 

Ref: http://www.sqlservercentral.com

How to Fix “Error: 823” in Microsoft SQL Server

Are you getting system-level errors while accessing your Microsoft SQL Server database? Are you unable to perform any I/O (Input/Output) operation on your database? Well, the problem generally occurs due to either conflicts of Windows API used by SQL Server or SQL Server database corruption. It leaves your database in an unusable state and leads to severe data loss situations. In order to get your mission critical data back in such cases, you must go for SQL database recovery solutions.

For instance, you may run across an error message similar to the following one in Windows Application Event Log or Microsoft SQL Server ERRORLOG:

“2010-03-06 22:41:19.55 spid58 Error: 823, Severity: 24, State: 2. 2010-03-06 22:41:19.55 spid58 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000a72c0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\my_db.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”

When a program encounters the above error message while modifying or querying the data, error is returned to that program and SQL Server database connected is closed.

Root of the problem: The problem is caused by Windows APIs, used by Microsoft SQL Server. These APIs (such as WriteFile, ReadFile, WriteFileGather, and ReadFileScatter) are used to perform Input/Output operations. Upon completion of I/O operations, MS SQL Server examines for errors associated with API calls. When API calls betrays with OS error, you come across this behavior of SQL Server. The problem may also occur due to severe corruption to Microsoft SQL Server database.

Solution: Go through the below steps to recover SQL server database from corruption:

• Review suspect pages table.
• Check database consistency.
• Review Windows Application Event Log to find operating system and file system related errors.
• Restore database from current backup.

If the above methods fail to perform SQL Server recovery, you are required to opt for advanced third-party applications. The MS SQL recovery applications ensure absolute recovery by throughly scanning the database using advanced scanning algorithms. They offer easy and safe recovery with simple user interface and non-destructive behavior.

Stellar Phoenix SQL Recovery is the most efficient and result-oriented utility to offer assured recovery of corrupted database. This software is designed to restore corrupt databases of MS SQL Server 2008, 2005, 2000, and 7.0. It retrieves tables, reports, macros, forms, triggers, stored procedures, constraints, default constraints, and other MS SQL Server database objects.

Ref : hubpages.com

Cannot attach SQL MDF file due to corrupt or missing Log file (Error 1813)

Transaction Log is corrupted. The transaction log ate the 80GB hardisk space which was not normal, so I tried to detach the database, delete the log and rebuild it. Unfortunately the mdf file cannot be re-attach anymore. It prompts the following error:

"Error 1813: Could not open new database '. CREATE DATABASE is aborted.

Device activation error. The physical file name 'C:\ ...\db_name.LDF' may be incorrect."

After a long research for answer I finally arrived in to a solution. Here are the step-by-step procedure I created to resolve the problem:

1. Created a new database with same MDF and LDF name

2. Stop sql server and delete the new MDF file and copy the old MDF file

3. Started SQL Server and verify the status of the database. If the database status is suspect do the following scripts in Query Analyzer:

a. Override updates in Master Table

Use Master

Go

sp_configure 'allow updates', 1

reconfigure with override

Go

b. Get the value of the status column in sysdatabase table

select * from sysdatabases where name = 'db_name'

-- note the value of the status column for later use in # 6

begin tran

c. Set status of the database to suspect (327768)

update sysdatabases set status = 32768 where name ='db_name'

commit tran

d. Rebuild transaction log

DBCC rebuild_log('db_name','db_log_path')

e. Set the database to single user access

sp_dboption '', 'single user', 'true'

DBCC checkdb('db_name')

Go

f. Restore the status of the database in sysdatabases table

begin tran

update sysdatabases set status = previous_value where name = 'db_name'

-- verify one row is updated before committing

commit tran

Go

g. Restore the override property of Master table

sp_configure 'allow updates', 0

reconfigure with override

Go

4. Now open or refresh the enterprise manager you can now see the tables inside the fixed database.

If any problems persist, you can import all tables into temporary database and drop the original database. Create a new database with same name then re-import all the tables from the temporary database

Ref: fryan0911.com

Scripts to use XML to insert and update rows in a SQL Server table

Problem
Over the years I have had to adapt to many issues presented before me as a Database Administrator and I have gathered many scripts to deal with them. One that has become quite a topic is XML integration for transactions to the database. In developing this process, my research of resources on the topic throughout the internet, seemed to only discuss primary functions and fundamental application. The following production code is what I used for inserting and updating database tables using XML as the input. These scripts are for processing data for any table to insert or update data. The support functions provided, retrieve the table schema with their data types, functions to deal with XML dates, primary keys of the table and what fields can be updated. The following article breaks down this process from beginning to end.

Solution
First, we need to deal with what data is in the transaction and the format it needs to conform to. For this example, I will be using the pubs database and work with transactions to authors.

Basically, the correctly formatted XML is generated with the table name (authors) as the parent node, rows are delimited by and the fields are the child nodes, as shown below.

                172-32-1176        White        Johnson        408 496-7223        
10932 Bigge Rd.
        Menlo Park        CA        94025        1          

Note: XML is case-sensitive therefore whatever you have for table names and column names must exactly match the XML.

For those wondering how this is done auto-magically, this select query will generate a sample XML document from the authors table in the pubs database:

SELECT
(
SELECT
(
SELECT au.*
FROM authors au
WHERE au_lname = 'White'
FOR XML PATH('Row'), ELEMENTS, TYPE
) FOR XML PATH ('authors'), TYPE
) "*" FOR XML PATH('BookInfo')

Now that we know what we are working with, we can move onto processing the transaction.

I have two processes one for inserting data and another for updating data. I will walk through the Insert and Update processes, so you have a better understanding of how the stored procedures work.

---

INSERT

In this section, I will walk through the different processes within the Insert stored procedure, which are to extract the NodePath and Table names from the XML, retrieve the schema datatypes, primary keys for that table, prepare the statement and then lastly loop through the XML to perform the request. You can download all of the code for this tip.

The following breaks apart the INSERT stored procedure and I'll explain each step in the process.

This is the first part of the procedure.

-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for INSERT
-- Dependencies: [dbo].[fnGetTableSchemaInsert], [dbo].[fnSetTableSchemaSelect],
[dbo].[fnGetTableSchemaSelectInto],[dbo].[fnIsDate]
-- =============================================
CREATE PROCEDURE [dbo].[prXMLDataInsert]
(
@XmlData XML
)
AS
BEGIN
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @hdoc INT
-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData

After testing several XML methods, the quickest way I found was to use the sp_xml_preparedocument then insert the results into a temporary dataset in order to quickly query values from the XML to process.

-- Set Raw XML Schema
SELECT *
INTO #xmlDoc
FROM OPENXML( @hdoc, '//*',2)

Once we have the dataset, our example XML data would appear as below:

The next step is to get the NodePath and table names from our temp dataset by using the query below:

-- Set Primary Table to use
SELECT DISTINCT IDENTITY(INT,1,1) id, rt.localname + '/' + tbl.localname + '/'
+ col.localname AS NodePath, tbl.localname AS NodeRow
INTO #xml
FROM #xmlDoc rt
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID AND rt.parentID IS NULL
INNER JOIN #xmlDoc col
ON tbl.id = col.parented

The results of the query would then appear as follows:

At this point, we are ready to loop through the XML data as illustrated below. A few points of interest, I used a while loop verses a cursor to keep down the resource overhead and keep the speed up, and for those that haven’t learned the hard way with sp_executesql since we have to pass in the XML into the statement, we only have nvarchar(4000) to store the statement, which causes chaos when you have tables with many columns.

DECLARE @i INT, @NodePath VARCHAR(255), @NodeRow VARCHAR(50),
@NodeKeys VARCHAR(255), @NodeCol VARCHAR(2000),
@UpdateNodes VARCHAR(2000), @sSql NVARCHAR(4000),
@SetSchemaSelect VARCHAR(4000), @iVars VARCHAR(2000)

-- Set id of first row
SELECT @i = MIN(id) FROM #xml

-- Begin looping through xml recordset
WHILE @i IS NOT NULL
BEGIN
SELECT @NodePath = NodePath, @NodeRow = NodeRow FROM #xml WHERE id = @i

What I found in order to build out the statements correctly, we need to get the column schema for the table were inserting into, but not any auto increment keys. Build the SELECT statement for the INTO portion of the statement and our OPENXML WITH fields with the correct datatypes, using the following functions illustrated below.

-- Get Table Schema for XML data columns
SELECT @NodeCol =[dbo].[fnGetTableSchemaInsert](@NodeRow)
SELECT @SetSchemaSelect = [dbo].[fnSetTableSchemaSelect](@NodeRow)
SELECT @ivars = [dbo].[fnGetTableSchemaSelectInto](@NodeRow)

DECLARE @param NVARCHAR(50), @pkID INT, @pkIDOUT INT
SET @param = N'@hdoc INT, @pkIDOUT INT OUTPUT'

For our sp_executesql we need to SET the @hdoc and can pass the identity value back if there is one for the table. As we continue, we have set our INSERT variables and now can build our final statement to execute.

-- This updates xml Recordset on primary keys of a given table
SET @sSql = 'INSERT INTO ' + @NodeRow + '(' + @iVars + ') SELECT ' + @SetSchemaSelect +
' FROM OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' + @NodeCol + ') as xm SELECT @pkIDOUT = SCOPE_IDENTITY()'

A sample of the final statement generated from the above line of code would appear as:

INSERT INTO authors(au_id,au_lname,au_fname,phone,address,city,state,zip,contract) SELECT [au_id],[au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract] FROM OPENXML( @hdoc,'BookInfo/authors/Row',2) WITH ([au_id] VARCHAR(11), [au_lname] VARCHAR(40), [au_fname] VARCHAR(20), [phone]CHAR(12), [address] VARCHAR(40), [city] VARCHAR(20), [state] CHAR(2), [zip] CHAR(5), [contract] bit) AS xmSELECT @pkIDOUT = SCOPE_IDENTITY()

Note: Here is where we can get in trouble with the nvarchar(4000) limit when the statement is dynamically assembled.

/******* Execute the query and pass in the @hdoc for update *******/
EXEC sp_executesql @sSql, @param, @hdoc, @pkIDOUT=@pkID OUTPUT

/***** Movenext *****/
SELECT @i = MIN(id) FROM #xml WHERE id > @I
END

-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml
END

---

UPDATE

Well I have explained the XML INSERT, now to we can step through the UPDATE process, which fundamentally goes through the same steps as the INSERT, but the functions for this stored procedure generate update values for the sp_executesql loop.

-- =============================================
-- Author: WA
-- Create date: 12/24/2009
-- Description: Translate XML to SQL RecordSet for UPDATE
-- Dependencies: [dbo].[fnGetTableSchema], [dbo].[fnGetTableUpdate],
-- [dbo].[fnGetPrimaryKeys],[dbo].[fnIsDate], [dbo].[fnGetTableKeys]
-- =============================================
ALTER PROCEDURE [dbo].[prXMLDataUpdate]
(
@XmlData XML
)
AS
BEGIN
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

DECLARE @hdoc INT
-- Prepare XML document
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlData
-- Set Raw XML Schema
SELECT *
INTO #xmlDoc
FROM OPENXML( @hdoc, '//*',2)

-- Set Primary Table to use
SELECT DISTINCT IDENTITY(INT,1,1) id, rt.localname + '/' + tbl.localname + '/'
+ col.localname AS NodePath, tbl.localname AS NodeRow
INTO #xml
FROM #xmlDoc rt
INNER JOIN #xmlDoc tbl
ON rt.id = tbl.parentID AND rt.parentID IS NULL
INNER JOIN #xmlDoc col
ON tbl.id = col.parented

DECLARE @i INT, @NodePath VARCHAR(255), @NodeRow VARCHAR(50), @NodeKeys VARCHAR(255), @NodeColVARCHAR(4000), @UpdateNodes VARCHAR(4000), @sSql NVARCHAR;-- -

--Set id of first row
SELECT @i = MIN(id) FROM #xml;

-- Begin looping through XML recordset to check for exists
WHILE @i IS NOT NULL
BEGIN
SELECT @NodePath = NodePath, @NodeRow = NodeRow
FROM #xml
WHERE id = @i

-- Get Table Schema for XML data columns
SELECT @NodeCol = [dbo].[fnGetTableSchema](@NodeRow)
SELECT @UpdateNodes =[dbo].[fnGetTableUpdate](@NodeRow)
SELECT @NodeKeys = [dbo].[fnGetPrimaryKeys](@NodeRow)

Just as the INSERT our sp_executesql will need to SET the @hdoc parameter and optionally pass the identity value back if there is one for the table.

DECLARE @param NVARCHAR(50)
SET @param = N'@hdoc INT'

At this point, we have set our UPDATE variables and can now build our final statement to execute.

SET @sSql = 'UPDATE ' + @NodeRow + ' SET ' + @UpdateNodes + ' FROM
OPENXML( @hdoc, ''' + @NodePath + ''',2) WITH (' +
@NodeCol + ') as xm INNER JOIN ' + @NodeRow + ' ON ' +
@NodeKeys

A sample of the final statement generated would appear as:

UPDATE authors SET [au_lname] = xm.au_lname, [au_fname] = xm.au_fname, [phone] = xm.phone, [address] =xm.address, [city] = xm.city, [state] = xm.state, [zip] = xm.zip, [contract] = xm.contract FROM OPENXML(@hdoc, 'BookInfo/authors/Row',2) WITH ([au_id] VARCHAR(11), [au_lname] VARCHAR(40), [au_fname] VARCHAR(20),[phone] CHAR(12), [address] VARCHAR(40), [city] VARCHAR(20), [state] CHAR(2), [zip] CHAR(5), [contract] bit)AS xm INNER JOIN authors ON authors.au_id=xm.au_id

Note: Let me pass a few words of wisdom concerning the OPENXML and WITH function. First the XML field values are all for practical purposes varchar, which is why we declare the Datatypes in the WITH section. When we have a datetime value to process, a value of NULL is translated to 1900-01-01 00:00:00.000, which is why I created the fnIsDate() function to turn it back to NULL.

-- Execute the query and pass in the @hdoc for UPDATE

EXEC sp_executesql @sSql, @param, @hdoc
/***** Movenext *****/
SELECT @i = MIN(id) FROM #xml WHERE id > @I
END

Finally, need to insure that we release the variables before returning

-- Release @hdoc
EXEC sp_xml_removedocument @hdoc
DROP TABLE #xmlDoc
DROP TABLE #xml
END

---

Putting It Together

Now that we have walked through the code for the Insert and Update these are functions that are required and are all part of the code download.

Functions:

• [dbo].[fnGetTableSchemaInsert]
• [dbo].[fnSetTableSchemaSelect]
• [dbo].[fnGetTableSchemaSelectInto]
• [dbo].[fnIsDate]
• [dbo].[fnGetTableSchema]
• [dbo].[fnGetTableUpdate]
• [dbo].[fnGetPrimaryKeys]
• [dbo].[fnGetTableKeys]

Stored Procedures:

• prXMLDataInsert
• prXMLDataUpdate

Examples

After you have created all of these objects in your database (in our case the pubs database) we can then use them to create new records or update existing records.

In this first example I have modified the fname to be "John" and the lname to be "Doe".

SELECT * FROM authors WHERE au_id = '172-32-1176';

EXEC dbo.prXMLDataUpdate
'


172-32-1176
John
Doe
408 496-7223

10932 Bigge Rd.

Menlo Park
CA
94025
1


';

SELECT * FROM authors WHERE au_id = '172-32-1176';

Here are the before and after values in the table.

In this example we will create a new author with an ID = '999-99-9999'

SELECT * FROM authors WHERE au_id = '999-99-9999';

EXEC dbo.prXMLDataInsert
'


999-99-9999
Doe
John
408 496-7223

10932 Bigge Rd.

Menlo Park
CA
94025
1


';

SELECT * FROM authors WHERE au_id = '999-99-9999';

Here are the before and after values in the table.

Ref: http://www.mssqltips.com/tip.asp?tip=2118