Saturday, February 19, 2011

Sql Server Error 8951 Data row does not have a matching index row in the index



The error 8951 emerges most often when there is a duplicate row exists on PK. You would not be able to find the duplicate row

till you drop the PK and try as follows:

Let's Understand: There is a simple table EmployeeInfo where the PK is created on Empid, Rid, KeyInfo, DateTimeSlot columns. There are duplicate rows in the table for the columns on which PK is established but following query throws only one value for the following query even though two rows exist:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot
HAVING COUNT(1) > 1

Let's drop the PK and run the above query again. This shows me duplicate values for the columns on which PK is created. You need to filter the bad data out or modify these rows to avoid this info.

Let's again restore the same DB on a Test server and use following command for any specific value where this had duplicate rows:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and KeyInfo = 'ACAF387865567973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'

This doesn't show the duplicate rows but following query will show you the data

select Empid, Rid, KeyInfo, DateTimeSlot,count(bit_delete)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and LTRIM(RTRIM(KeyInfo)) = 'ACAF31DBC69556EA9A973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot

Following is the data:



Empid Rid KeyInfo DateTimeSlot (No column name)
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1

The problem is in your DateTimeSlot column. drop the PK and this will catch where your duplicate data is.

Regards,
http://tuitionaffordable.webstarts.com

Tuesday, February 8, 2011

Restoring the stripped backup Files







The blog can give you good understanding on "MSSQLServer stripped backup".


Why anyone wants to go to stripped backups?
The answer is that when you have your drives on SAN and you want to use the I\O

from each more than one SAN while taking the backup then this is a good option to go for the stripped backups. Another point is that in some scenario the free space in one drive is not enough to accomodate your complete backup file.

After you are clear on why you decide stripping the backup file, lets talk on how to stripe the backupfile.

Command to create the stripped backups

BACKUP  TO
DISK
= 'E:\MSSQL\BAK\Tuitionaffordable_1of2.BAK'

,DISK = 'J:\MSSQL\BAK\Tuitionaffordable_2of2.BAK'
WITH  INIT

Let's go ahead and restore the command

RESTORE   DATABASE Tuitionaffordable
WITH   RECOVERY, REPLACE



FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK'

,DISK = 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK'

There is one more important thing. In case your destination server doesn't have enouth free space in the corresponding drives then above command will fail. Backup file has the information where it needs to create the mdf, ndf and ldf files. Let's take a scenario when you are going to MOVE all the files to only one drive eg K because you have enough free space only in K drive of your destination server. The command will be as follows:

RESTORE DATABASE Tuitionaffordable
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK',

DISK= 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK',
WITH RECOVERY, REPLACE  

MOVE 'Tuitionaffordable' TO 'K:\MSSQL\Test\Tuitionaffordable.MDF',
MOVE 'Tuitionaffordable_Data2' TO 'K:\MSSQL\Test\Tuitionaffordable_Data2.NDF',
MOVE 'Tuitionaffordable_Data3' TO 'K:\MSSQL\Test\Tuitionaffordable_Data3.NDF',
MOVE 'Tuitionaffordable_Data4' TO 'K:\MSSQL\Test\Tuitionaffordable_Data4.NDF',
MOVE 'Tuitionaffordable_Data5' TO 'K:\MSSQL\Test\Tuitionaffordable_Data5.NDF',
MOVE 'Tuitionaffordable_Data6' TO 'K:\MSSQL\Test\Tuitionaffordable_Data6.NDF',
MOVE 'Tuitionaffordable_Data7' TO 'K:\MSSQL\Test\Tuitionaffordable_Data7.NDF',
MOVE 'Tuitionaffordable_Data8' TO 'K:\MSSQL\Test\Tuitionaffordable_Data8.NDF',
MOVE 'Tuitionaffordable_Data9' TO 'K:\MSSQL\Test\Tuitionaffordable_Data9.NDF',
MOVE 'Tuitionaffordable_Index' TO 'K:\MSSQL\Test\Tuitionaffordable_Index.NDF',
MOVE 'Tuitionaffordable_Index2' TO 'K:\MSSQL\Test\Tuitionaffordable_Index2.NDF',
MOVE 'Tuitionaffordable_Log' TO 'K:\MSSQL\Test\Tuitionaffordable_Log.lDF'