How to perform SQL database recovery in case of MS SQL Server Error 9004

A transaction log is an important component of database which is often ignored or overlooked as it is not regularly maintained or used by the database administrators or users. It keeps track of all the changes made to the database and maintains a separate data file to store the actual data. Transaction log backup and the corresponding data file backup can be used to perform database recovery in case of a corruption and restore the database to its original state. But the transaction log may get corrupt due to several reasons, such as installing wrong drivers, virus attack, hard drive failure, system table corruption etc. In such situations, the transaction log may become inaccessible and thus, you may not be able to restore your database. To overcome this problem, you need to go for SQL database recovery through a reliable third party utility.

While trying to access a corrupt transaction log, you may see the following error in ERRORLOG or Windows Application Event Log:

"Error: 9004, Severity: 21, State: 1.

An error occurred while processing the log for database 'mydb'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log."

Cause:

This error may have occurred due to any of the following reasons-

The file system is corrupt.
A hardware error
Damaged drivers
SQL Server Engine problem

The transaction log corruption may also lead to a corrupt database. To recover back your lost data in case of any corruption scenario, you need perform SQL recovery by following below mentioned resolution steps.

Resolution:

Try to restore the transaction log from a backup. If you are unable to recover it from a backup, you may try to rebuild the log and bring it on-line.

Rebuilding of the transaction log should be done carefully as it can result in transactional consistency loss in the database. In all these circumstances, you should take help of a reliable database recovery software in order to recover back your lost valuable data from the corrupt database. These software are capable of repairing those database files as well that are not repaired by the DBCC CHECKDB tool.

Stellar Phoenix MSSQL Database Recovery Software is a recovery utility that repairs and restores MS SQL Server databases from all scenarios of corruption. It supports MS SQL Server 7.0, 2000, 2005, and 2008. It can recover all database components including tables, views, rules, user defined functions, indexes, constraints etc. It performs SQL repair by allowing you to search for the corrupt MDF file in your system.

Continue Reading

Resolving Table Error - Object Id O-ID - Possible Bad Root Entry in Sysindexes

The metadata structure of SQL Server database comprises of numerous Index pages, which further consist of data pages. The data pages at each level of Index page are linked in a doubly-linked list. Inaccessibility of data stored in the data pages primarily occurs due to breaking of linked list (metadata structure corruption). Additionally, it also results in unmountability of the database. Few main reasons for metadata structure damage are improper system shutdown, virus hits, or physical damage. An easy solution to overcome the metadata structure corruption situation, you will need to restore the data from an updated database backup. However, with case studies proving that database users tend to forget to create an updated database backup, it is always handy to take help of third-party sql database repair applications.

Picture a scenario, where you receive the below error message while you attempt to mount your SQL Server database:

"Table error: Object ID O_ID, index ID I_ID. Page P_ID1 is missing references from parent (unknown) and previous (pageP_ID2) nodes. Possible bad root entry in sysindexes."

The result of the above error message is unmountability of your database, which further results in inaccessibility of database records.

Cause

The root cause for the occurrence of above error message is not linking of P_ID1 to the B-tree. The message can be caused either due to hardware or software issues.

Resolution

To resolve the above error message and to access the database records, you will need to follow the below steps:

Change the physically damaged system component with a new system component to resolve all physical damage problems.

Execute DBCC CHECKDB command to resolve logical corruption issues.

While the former resolution proves successful in resolving all physical corruption scenarios, the later fails to resolve all logical corruption issues. For resolving every logical corruption problem, you will need to use advanced SQL Database Repair software. These sql recovery tools repair corrupt SQL database by using advanced scanning and repairing algorithms. Additionally, these applications perform repair without deleting or modifying the original content of the database.

SQL Recovery comprehensively repairs and restores database created in SQL Server 2008, 2005, and 2000 versions. The sql database repair software is designed for Windows 7, Vista, XP, 2003, 2000 and NT operating systems. It repairs almost all database objects, including tables, stored procedures and views, and supports.

Source

Continue Reading

Failing to restore SQL Server database backup with internal consistency error

Here is one of the corruption issue that i came across on the database which was dettached after entering into suspect mode making it difficult to re-use the mdf and ldf file.
Even the database backup they had was taken from SQL 7.0 instance which had corruption. This backup was taken post the database was corrupt.
Below im sharing the information on all the steps that I tried and finally getting the backup work with minimal data loss.

Failing to restore SQL Server database backup with internal consistency error.

ERROR
======
5 percent restored.
10 percent restored.
15 percent restored.
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Troubleshooting
===============
-- Customer had SQL 7 backup and trying to restore the database and failing to restore and giving some internal consistecny error.
-- It passes all the checks (Verify Header and Filelistonly) however when trying to restore.
-- CU do not have the DB anymore in production.
-- They have mdf and ldf but they were dettached after the database went into suspect mode.

-- Ran Restore headeronly from disk = backup file name, this came with below output.

BackupName BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize
--------------- ---------- ------------------ ---------- -------- ---------- ------------------------- -------------- ----------------- --------------- ---------------------------------- ----------------------
NULL 1 NULL 0 1 2 515 2009-01-02 13:54:13.000 2516844032

-- Restore Verifyonly -- Gave Valid backup Set.
-- restore filelistonly -- Gave the result.
-- This was giving indication that the backup is good but still it was giving some inconsistency issue.
-- Looks like the Database backup was taken post the database encoutnered corruption.
-- Connected to SQL 2005 instance and try restoring the database from the backup here.
-- We saw that everytime we try to restore the DB using the back file, we saw the DB files getting created but it was failing to complete the restore.
-- Ran the restore using WITH CONTINUE_AFTER_ERROR paramter, this continued file with no errors.
-- This completed fine but it detected inconsistency error below:

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Processed 307200 pages for database 'DBName', file 'DBName_Data' on file 1.
Processed 1 pages for database 'DBName', file 'DBName_Log' on file 1.
100 percent processed.
Damage to the backup set was detected.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 307201 pages in 142.521 seconds (17.657 MB/sec).


-- Checked SQL Server error from file.

2010-03-15 11:41:57.15 spid63 Starting up database 'DBName'.
2010-03-15 11:41:57.20 spid63 The database 'DBName' is marked RESTORING and is in a state that does not allow recovery to be run.
2010-03-15 11:41:58.79 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:02.09 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:02.24 spid63 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2010-03-15 11:42:32.23 spid63 Error: 3183, Severity: 16, State: 1.
2010-03-15 11:42:32.23 spid63 RESTORE detected an error on page (0:0) in database "DBName" as read from the backup set.
2010-03-15 11:44:12.56 spid63 Starting up database 'DBName'.
2010-03-15 11:44:12.58 spid63 The database 'DBName' is marked RESTORING and is in a state that does not allow recovery to be run.
2010-03-15 11:44:13.22 Backup Database was restored: Database: DBName, creation date(time): 2009/01/02(13:54:13), first LSN: 12375:571:29, last LSN: 12375:582:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\DIR\Backup\DBName_20100310_214921.bak'}). Informational message. No user action required.

-- In SSMS the status of the DB was restoring.
-- Ran --> Restore database with recovery, but failed with below error:

Server: Msg 4323, Level 16, State 2, Line 1
The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

-- Stopped SQL 2005 instabnce.
-- Copied the mdf and ldf file for this restoring state Database.
-- Dropped the DB in restoring state.
-- Tried to CREATE DATABASE DBNAme ON (Filename ='D:\File.mdf') FOR ATTACH_FORCE_REBUILD_LOG
-- This again failed with below error.

File activation failure. The physical file name "F:\Database\DBName\DBName_Log.LDF" may be incorrect.
New log file 'D:\MSSQL\SQL2005\Logs\DBNAme_log.LDF' was created.
Msg 1824, Level 16, State 1, Line 1
Cannot attach a database that was being restored.

-- Here I noticed that the log file was getting created on F Drive and we did not had Drive: F on the box.
-- Mapped another drive with letter F: and created the folder structure, but same error.

-- Tried EXEC sp_attach_single_file_db but failed with

File activation failure. The physical file name "F:\Database\DBName\DBName_Log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBNameDBName'. CREATE DATABASE is aborted.

-- Now tried the sp_attach_db command but failed again.

EXEC sp_attach_db @dbname = N'AdventureWorks',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf';


Msg 1824, Level 16, State 1, Line 1
Database cannot be restored.

-- Copied the original Data and Log files.
-- And tried attaching the files here.

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBName'. CREATE DATABASE is aborted.
Msg 9004, Level 21, State 1, Line 1
An error occurred while processing the log for database 'DBName'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.


RESOLUTION
==========
-- Finally resorted back to Restoring the database from SQL 7.0 backup taken with CONTINUE_AFTER_ERROR paramter.
-- Then ran sp_reset status follwoed by restore with recovery this got the DB in accessible mode and was able to read the table.
-- Ran CHECKDB and this showed inconsistency on user Table.
-- Ran repair and this fixed the corruption, however there was some data loss which could not be recovered.

Article Source

Continue Reading