Thursday, December 30, 2010

Extract Eventlog Information Using Powershell



Who Should Read This: DBA or System engineer who wants to gather eventlog information after any important release.

Following blog can help the DBA to gather important information step by step.

Scenario 1: DBA needs to gather the eventlog for last 24 hours on one particular server.

# Declaring one date variable which is intialized with a time which is 24 hours ago.
$dt1 = ((Get-Date).AddHours(-24))

# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername servername -after $dt1 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

Custom Search
get-eventlog system -computername servername -after $dt1 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append


Scenario 2: DBA needs to gather the eventlog between two specific dates on one particular server.

# Declaring two date variables which is intialized with two time when DBA needs to capture the eventlog.
$dt1 = ((Get-Date).AddHours(-240))
$dt2 = ((Get-Date).AddHours(-220))

# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername servername -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

get-eventlog system -computername servername -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append

Scenario 3: DBA needs to gather the eventlog between two specific dates on a list of servers.

#Creating a server list and retrieving all the servers into variable $coreservers
$coreservers = GET-CONTENT "D:\EventLog\serverlist_BE.txt"

# Declaring two date variables which is intialized with two time when DBA needs to capture the eventlog.
$dt1 = ((Get-Date).AddHours(-240))
$dt2 = ((Get-Date).AddHours(-220))
foreach($server in $coreservers)
{
# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername $server -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

get-eventlog system -computername $server -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append
}

Scenario 4: DBA needs to identify only count of Errors from each user
get-eventlog -log system -EntryType "Error" | group-object -property username -noelement | format-table Count, Name -auto

Scenario 5: DBA needs to identify the count of Errors, Information and warnings
get-eventlog -log system | group-object -property entrytype -noelement | format-table Count, Name -auto



DB Log File Full And DBCC LOGINFO Shows Numerous Virtual Logs







Follow the steps to resolve the issue when your DB is in simple recovery mode and the log file is full. Also this is applicable in a special case when there are numerous (>2000) virtual logs inside your transaction log.

1.  Find log files of the DB whose log file is huge
        SELECT * FROM sys.database_filesWHERE type_desc = 'LOG'


2. Find the log file usage as follows
         DBCC SQLPERF (LOGSPACE)


3. Find the number of Files in the log file. This depends on how the growth of the log file. If growth is very less then the count would increase. The status = 2 => the file can't be shrunk.
       DBCC LOGINFO (Conductor)


4.. Offline DB

5. Move the log files

6. Take DB online

7. Set Emergency

8. Put the DB in Single User Mode

Custom Search
        ALTER DATABASE Conductor SET SINGLE_USER WITH ROLLBACK     IMMEDIATEALTER DATABASE Conductor SET EMERGENCY

9. Rebuild Log
       DBCC CHECKDB (Conductor, REPAIR_ALLOW_DATA_LOSS)


10. Put the DB in Multi User Mode
         ALTER DATABASE Conductor SET MULTI_USER WITH ROLLBACK IMMEDIATE




Wednesday, December 29, 2010

SQL Server LiteSpeed Restore DB and Familier Errors




Search Engine OptimizationSubmit Express
SQL Server LiteSpeed Restore DB and Familier Errors

Who should read this blog: MS SQL DBA to get familier with Litespeed Restore process and understand important litespeed errors.

First I shall try to treat lite speed backup file as I native backup. This throws Sql server error 3241.

RESTORE DATABASE LiteSpeedDB
FROM DISK = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Let's Understand: Install litespeed and try to restore the DB using extended stored procedure xp_restore_database. Following statement throws error. Go through the error and resolution as follows:

EXEC master..xp_restore_database
@database='LiteSpeedDB',
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

Msg 61700, Level 16, State 1, Line 0
Unexpected termination on thread: 0, Return code: x80770004

RESTORE DATABASE is terminating abnormally.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
File 'LiteSpeedDB_Log' cannot be restored to 'o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB_Index' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).

Let's Understand:  Sql server error 61700 and operating system error 3 indicates that the files of this DB reside on the drives which doesn't exist on this server. eg. The files of the DB resides in H and I drive but your system doesn't have these files.

Custom Search
EXEC master..xp_restore_filelistonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Let's verify the backup file:

EXEC master..xp_restore_verifyonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

The backup set on file 1 is valid.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
The path specified by "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" is not in a valid directory.
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

CPU Seconds: 0.28
Environment: Intel(R) Pentium(R) M processor 1.86GHz
CPUs: 1 logical, 1 core, 1 physical package.

The backup file is valid.

Let's Understand The above information indicates that the backup file is valid. Directory lookup fails with "operating system error 3" because there are three files in the original DB but the directory for these files are missing on the server.

EXEC master.dbo.xp_restore_database @database = 'LiteSpeedDB'
, @filename = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'
, @with = 'MOVE "LiteSpeedDB" TO "D:\BackupLiteSpeedDB\LiteSpeedDB.MDF"'
, @with = 'MOVE "LiteSpeedDB_Index" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_Index.NDF"'
, @with = 'MOVE "LiteSpeedDB_Log" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_log.LDF"'

The DB is restored perfectly.

Sometimes you also get following error:
SQL LiteSpeed Error: XML returned from Engine was invalid

Let's Understad:  This error indicates that something happened at sql server end and litespeed is not able to understand what is returned from sql server.
How to Resolve: Most of the time excessive blocking, h\w issues and many other things under the sun. I would prefer to go for a quick health check and run the command again.

Also Read: Comparison between Litespeed Backup and Native Backup Compression


Regards,
http://tuitionaffordable.webstarts.com

Monday, December 27, 2010

SQL Server Error Msg 2570 - type "In-row data". Column value is out of range for data type



Who Should Read This Blog: MSSQL Server DBA who needs to get detailed knowledge about resolving DB corruption.

Walk through following blog which deals with from corrupt DB restore to fixing the DB corruption.

Here I am trying to restore a databse for which backup is stored in D:\TuitionaffordableDBs\TuitionafordableDBBackups folder.
Before giving restore command we should always verify the backup as bellow-

RESTORE VERIFYONLY
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

I got the following error msg as a result of this command. This will be a good learning for you.

"Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details."

The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf" is not in a valid directory.
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.

Let's Analyze: It seems that there are some storage problem in C: drive. But there was enough space in C: Drive for this backup when I checked.
Means??? there are some other isse which I need to address.

I tried with below restore command to get the actual error-

RESTORE DATABASE TuitionAffordable1
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

Now get the bellow error msg-

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'TuitionAffordable1' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'TuitionAffordable1_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TuitionAffordable1_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Let's Analyze: The path "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA" is missing where new .mdf needs to be created. So I Created the path as shown in the error and executed 'VERIFYONLY' once again.

The backup set on file 1 is valid. --The backup file is valid.

Custom Search

Try now-

RESTORE DATABASE TuitionAffordable1
FROM DISK = 'D:\TuitionaffordableDBs\TuitionafordableDBBackups\TuitionAffordable1.bak'

And the DB got restored perfectly. Let's walk through this DB and find the corruption.

DBCC CHECKDB (TuitionAffordable1)

Below is the error-
Msg 2570, Level 16, State 3, Line 1
Page (1:24433), slot 91 in object ID 421544440, index ID 1, partition ID 72053332339697408, alloc unit ID 12127594044809216 (type "In-row data"). Column "AvgVal" value is out of range for data type "float". Update column to a legal value.

Let's Analyze: Find out the object name for the object ID present in the error msg-
SELECT OBJECT_NAME(421544440)

Got the object name as "Salary". Go ahead as following-

SELECT * FROM Salary

It also gave an error-
An error occurred while executing batch. Error message is: Arithmetic Overflow.

Which means there is some problem in data type of column AvgVal of Table Salary. Change the data type to DECIMAL.

ALTER TABLE PRODUCTS
ALTER COLUMN PRICE DECIMAL (10,5)

Problem is resolved

Trying another method to fix the corruption

DROP DATABASE TuitionAffordable1

Msg 3702, Level 16, State 3, Line 1
Cannot drop database "TuitionAffordable1" because it is currently in use.

--Any repair statement needs DB to be in single user mode.

Change the connection to master

DBCC CHECKTABLE (PRODUCTS, REPAIR_ALLOW_DATA_LOSS)

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

I change the database in single user mode-
ALTER DATABASE Tuitionaffordable1
SET SINGLE_USER WITH NO_WAIT

And then try to restore the DB but again it throws the same error of arithmetic overflow. So here we determined that repairing DBCC CHECKTABLE (PRODUCTS, REPAIR_ALLOW_DATA_LOSS) sometime is not the last resort.
We should always analyse the error before proceeding.


Sunday, December 26, 2010

SQL Server 2008 R2 - Foreign Key - DELETE CASCADE and DELETE UDPATE

Following blog travers you through step by step information about creating FKs and ON DELETE Action.

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT
)

--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT REFERENCES EMPLOYEEINFO(DeptId)
,Deptname VARCHAR(20)
)

You get following error while creating the FK because the DeptId in EmployeeInfo is neither PK nor unique. An FK doesn't have to be linked only to a PK in another table. It can also reference to a UNIQUE constraint in another table.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'EMPLOYEEINFO' that match the referencing column list in the foreign key 'FK__DeptInfo__DeptId__08EA5793'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT UNIQUE
)
--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT PRIMARY KEY
,Deptname VARCHAR(20)
)

ALTER TABLE DeptInfo
ADD CONSTRAINT FK_DeptInfo_DeptId FOREIGN KEY (DeptId)
REFERENCES EMPLOYEEINFO(DeptId)

SELECT *
FROM    sys.foreign_keys
-- There are two columns which define the delete and update action. The columns are delete_referential_action_desc and update_referential_action_desc.

0 - NO ACTION
1 - CASCADE
2 - SET NULL
3 - SET DEFAULT


Custom Search
--Inserting data in these tables

INSERT INTO DeptInfo VALUES(1,'HR')
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DeptInfo_DeptId". The conflict occurred in database "TestDB", table "dbo.EMPLOYEEINFO", column 'DeptId'.
The statement has been terminated.

INSERT INTO EMPLOYEEINFO VALUES(1,'tuitionaffordable','HR')

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'EMPLOYEEINFO' can only be specified when a column list is used and IDENTITY_INSERT is ON.

INSERT INTO EMPLOYEEINFO VALUES('tuitionaffordable',1)
INSERT INTO DeptInfo VALUES(1,'HR')

--Lets try to delete data from DeptInfo

DELETE Deptinfo -- Successful

But if you try to delete the information from Employeeinfo then you get error:

DELETE EMPLOYEEINFO
WHERE DeptId = 1

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_DeptInfo_DeptId". The conflict occurred in database "TestDB", table "dbo.DeptInfo", column 'DeptId'.
The statement has been terminated.

This error can be fixed with ON DELETE ACTION. There can be four types of actions "ON DELETE" and "ON UPDATE".

0 - NO ACTION
1 - CASCADE
2 - SET NULL
3 - SET DEFAULT

--1. Create a table Employeeinfo
CREATE TABLE EMPLOYEEINFO
(
Empid INT IDENTITY(1,1) PRIMARY KEY
,Empname VARCHAR(20)
,DeptId  TINYINT UNIQUE
)
--2. Create another table DeptInfo
CREATE TABLE DeptInfo
(
DeptId TINYINT PRIMARY KEY REFERENCES EMPLOYEEINFO(DeptId) ON DELETE CASCADE
,Deptname VARCHAR(20)
)

INSERT INTO EMPLOYEEINFO VALUES('tuitionaffordable',1)
INSERT INTO DeptInfo VALUES(1,'HR')

DELETE EMPLOYEEINFO
WHERE DeptId = 1 -- This will not throw any error. The DeptId 1 is also deleted from DeptInfo table.


Friday, December 24, 2010

SSRS Report - Adding Header and Footer



Following is a tip to add your costomized header and footer to the SSRS report.

1. Connect to your ssrs report and right click at the layout.







           2. Click on Page Header to add the header for SSRS. Now drag textbox to the header and write header info.
3. Click on Page Footer to add the footer for SSRS. Now drag textbox to the footer.

4. We shall add some function for footer. eg "Page X of Reportname". It goes as follows:


Custom Search
5. You can add the same function as follows. This will give you a footer ie "Page X of Reportname".


6. You can depoly your report and get costomized header and footer.



Thursday, December 23, 2010

MSSQL Server - DBCC LOGINFO - Status 2. Log File FULL. Verify log_reuse_wait_desc.



Following is a scenario when LOG file is full. Following steps are taken to resolve the problem:
DBCC LOGINFO (Tuitionaffordable) -- Too big

-- This command gives you large data. The status 2 is uncommitted transactions.

BACKUP LOG Tuitionaffordable WITH TRUNCATE_ONLY -- Or NO_LOG

Msg 155, Level 15, State 1, Line 1
'truncate_only' is not a recognized BACKUP option.

-- This was good enough till 2005 but the command is depricated in 2008. Sql server forces you to take backup of t-logs to maintain integrity and consistency.

Try following command to get the 'Log Space (% used).
DBCC SQLPERF (logspace) - 100% Log Space Used for DB tuitionaffordable

People would go ahead and run CHECKPOINT. Problem is as follows when log is 100% full:

Msg 9002, Level 17, State 6, Line 1
The transaction log for database 'Tuitionaffordable' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Actually CHECKPOINT writes all dirty pages for the db to disk. These are data pages which are right now in the buffer cache and modified, but not yet written to the disk. The Checkpoints are created which save time during a later recovery by creating a point at which all dirty pages are surely written to disk.

I would advise to create a new log file with small size and run the CHECKPOINT again.

My DBA tried taking backup without giving any breathing space to DB and got following error:

Date  12/23/2010 8:39:34 AM
Log  Job History (AdHocBackup)
Step ID  1
Server  Tuition
Job Name  AdHocBackup
Step Name  AdhocBackup
Duration  00:00:01
Sql Severity  17
Sql Message ID  3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted  0

Custom Search
Message
Executed as user: SQL\Tuitionaffordable. The transaction log for database 'Tuitionaffordable' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

Now you can try following command to take the backup of the log:

BACKUP LOG Tuitionaffordable TO DISK =  'E:\mssql\bak\Tuitionaffordable_log.trn'

You will surely get an error if the recovery model is simple otherwise your LOG will get truncated.
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

If you stil face trouble, I would ask you to find out the open transaction. You will find some long open transaction which is not committed yet.

DBCC OPENTRAN

Transaction information for database 'Tuitionaffordable'.
Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (983549:5658:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Go ahead and find the related spid for this using following command.

select spid,open_tran from master..sysprocesses
where  open_tran <> 0

I would prefer to kill the spid only after confirming the text under this.
use http://tuitionaffordable.wordpress.com/sql-server-dba-interview-questions - Answer 7


Tuesday, December 21, 2010

MSSQL Corruption Msg 2627, Level 14, State 1, Line 1 - Violation of PRIMARY KEY constraint"



SQL Corruption can cause enough hue and cry in the team. The reason is very natural because sometimes the corruption leads to data loss. All the corruptions are not easy to deal with. Sometimes you neglect the long lingering corruption and this cost you badly with its ugly face.

The same happened yesterday night when I got call about corruption on DB. My DBA already found out the three corrupted tables.

I proceeded as follows to tackle corruption:

-- Confirming the Corruption
DBCC CHECKTABLE (Table1)     
DBCC CHECKTABLE (Table2)     
DBCC CHECKTABLE (Table3) 
Index corruption can’t be fixed by dropping and recreating indexes because of following scenario.

Scenario 1:
Two records were violating the PK definition on Table1. We can't drop and recreate the PK index in this case. If the primary key is dropped then this would not be created again because the data is not unique here. The best way is to chage the data a bit. I went ahead and change the date column which was participating in the PK. And the issue was resolved.

Taking the DB in SINGLE_USER mode and using REPAIR_FAST to repair the corruption. (You have to have the DB in the single user mode to use REPAIR_FAST option.)

The result of REPAIR_FAST is as follows:

/*Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Tablename_01_9999'. Cannot insert duplicate key in object 'dbo.Tablename_01_9999'.
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Tablename_01_9999' and the index name 'PK_Tablename_01_9999'. The duplicate key value is (26, 0, 52222A90A7676710E8DB2A5ED4E8880D82, Jan  1 9999 12:00AM).

Fixing this with REPAIR_rebuild
DBCC CHECKTABLE (Table1, REPAIR_rebuild) --Not Repaired    
DBCC CHECKTABLE (Table2, REPAIR_rebuild) -- Not Repaired     
DBCC CHECKTABLE (Table3, REPAIR_rebuild) --Repaired

Table1 and Table2 both have the duplicate record for the PK and so REPAIR_REBUILD would not be able to repair that.

I got following error as a result of REPAIR_REBUILD:

Msg 8951, Level 16, State 1, Line 1
Table error: table 'Table1_01_9999' (ID 821577335). Data row does not have a matching index row in the index 'PK_Table1_01_9999' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (4:3035629:51) identified by (HEAP RID = (4:4435629:51)) with index values 'iId = 26 and iWindowId = 0 and chKeyHash = '52222A90A91710E8DB2A5ED4E1460D82' and dtTimeSlot = '9999-01-01 00:00:00.000' and HEAP RID = (4:3035629:51)'.

Custom Search
The corruption can't be fixed even with REPAIR_ALLOW_DATA_LOSS.

DBCC CHECKTABLE (Table1, REPAIR_ALLOW_DATA_LOSS ) --Not Repaired    
DBCC CHECKTABLE (Table2, REPAIR_ALLOW_DATA_LOSS ) -- Not Repaired     

The only method to fix this corruption is to update the PK column to make this unique. As soon as PK is unique, run the DBCC CHECKDB and you will find that the corruption is fixed.

Vulnerable Case: This kind of corruption often happens when you have date column involved in the PK.

Sometimes you are not able to map the sql page with the table. Here you go:
DBCC TRACEON (3604, -1)
GO
DBCC PAGE('DBName', Fileid, pageid, info-option)
GO
This will give you a lot more info along with ObjectId. Use this objectid to identify the object name.
SELECT OBJECT_NAME(objectid)

Recently following problem drove me nuts:

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 782621111, index ID 1, partition ID 72057512123039744, alloc unit ID 72057594050433404 (type In-row data). Page (1:9902) is missing a reference from previous page (1:12151). Possible chain linkage problem.
        The repair level on the DBCC statement caused this repair to be bypassed.
Actually repair_rebuild is the minimum repair level for these kind of errors. When I tried REPAIR_REBUILD, this cost me 50GB error log for a table of size less than 1 GB.

Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'TuitionaffordableDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The only way to resolve this problem was to recreate the index. While recreating you will find that the PK has duplicate rows. So clean them.


Regards,
http://tuitionaffordable.webstarts.com