How to rebuild corrupt transaction log?

In the previous article we have discussed about “how to restore MS SQL database without transaction log file”. In this article we have discussed about how to deal with error 9004 in Microsoft SQL server database. Error 9004 occurs when you try to attach SQL server database and the an error message appears look like

“Error: 9004, Severity: 21, State: 1.
An error occurred while processing the log for database 'employee'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”

Cause for Error 9004: This error message is related to the corrupt transaction log. It is a general error message that indicates there is some corruption in the transaction log. There can be several possible cause for above error like hardware error, software malfunction, corrupt drivers, SQL server engine problem etc.

Possible Solution: when you got error 9004, you have three options to resolve this error message.

First solution: you can restore from the clean backup to recover SQL server from above problem.

Second solution: if you can not recover from the backup then you can rebuild the transaction log. For creating transaction log, you should follow the given below steps:

• Create a new empty database with same name and physical file layout.
• Shutdown the SQL server database.
• Swap the files in empty database which you want to attach.
• If database goes in suspect mode then set database in emergency mode.
• Run run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.
• Fix the issues.

Third solution: You can also try “CREATE DATABASE FOR ATTACH_REBUILD_LOG”. This command will run successfully when you have shutdown SQL server properly.

Note: second solution will pull data as much as possible from the corrupt transaction log but be sure there may be some data loss.

Fourth solution: Use a powerful SQL Database Recovery application.

Continue Reading

Powerful MS SQL repair software to repair and recover your corrupt SQL database

Sometimes, while working in Microsoft SQL, it issues warning messages. A possible database corruption is the reason behind such alarming messages. Though many users ignore such messages and start experimenting with the database, which results in harsher instance of data loss or inaccessibility. Therefore, when your valuable data is lying in the database, handle it with extreme care and it would be wise to use any reliable SQL repair utility to repair the database and recover the data back.

After encountering the error or warning message, which gets flashed every time you try to open the database, the tables in the MS SQL database becomes completely inaccessible. Moreover, repeated occurrence of the error may result in more complex corruption of the SQL database. The error message that you may have come around can be as read as below:

Warning: Page P_ID, slot S_ID in Object O_ID Index I_ID Column COLUMN_NAME value COLUMN_VALUE is out of range for data type "DATATYPE". Update column to a legal value.

Cause:
The above error message tells that the particular column contains an illegal value, which can be caused by any logical or physical corruption of the SQL database.

Resolution:
In order to resolve the above case of database corruption and recover the data back from it, you have to run the command ‘DBCC CHECKDB’ with the recommended repair clause.

However, with severe corruption, this command fails to repair the database and you need to restore the database from any previously created valid backup. But, if you do not have any valid backup available, still you can recover the data by repairing SQL database with the help of any mdf repair software.

These MS SQL repair utilities are innovatively developed applications to successfully repair the SQL database and recover all the data from it. These applications are absolutely read-only in nature and recover database keeping the integrity intact. Moreover, with highly interactive and easy-to-use user interface, any user even without much technical knowledge can use the software and repair SQL database.

Stellar Phoenix SQL Recovery is a heavily used and highly appreciated SQL server recovery utility for its safe and efficient recovery of data from any instance of database corruption. The software is compatible with SQL Server 2008, 2005, 2000 and recovers the database tables, containers, schema, stored procedures etc without even altering their original format. Above all, this wonderful utility incorporates absolutely easy-to-use functionalities and provides a lucid recovery experience.

Continue Reading

Risk-free Sql Repair Utility To Retrieve Suspect Sql Database Issue

Sometimes, the Microsoft SQL server can not be able to access one of its database and sets the corresponding bit as something other than normal in the status field of the 'sysdatabases' table. After this, the particular database appears as 'SUSPECT' in the Enterprise Manager of the SQL server. When any database is marked as 'SUSPECT', you may encounter error messages while accessing the same. Moreover, the mounting of the database is halted with the error messages and the database becomes completely inaccessible. Under such situations, in order to bring back your important data from the troubled database, you need to use any utility which can repair SQL.

There can be some sort of corruption to the SQL database which has triggered SQL server to mark it as SUSPECT. In such case, the error message that you receive may be as below:

96/11/18 10:48:32.60 kernel udopen: Operating System Error 32 (The
process cannot access the file because it is being used by another
process.) during the creation/opening of physical device,
C:\DATA\SQL\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)
96/11/18 10:48:32.60 kernel udactivate (primary): failed to open
device C:\MSSQL\DATA\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF) for vdn 127

After the occurrence of the above error message, you will be unable to access the particular SQL database and the error pops up each time you try to access the database.

Apart from the database file corruption, it may be any missing device driver, renaming of the database, changing location of the database file, up-gradation of the operating system etc which can result in the inaccessibility of the database by the SQL server.

Resolution:

Below steps may help you in resolving the problem and help you access the troubled SQL database once again:
If it is the case of missing device driver file, ensure that the file is available.
If it is the case of a sure database corruption, below steps will guide you through the resolution process:
Drop all the indexes of the database and recreate them once again.
After the above step, copy every record from the original database table and paste them into a newly created one.
Update the statistics.
Run the command 'DBCC UPDATEUSAGE'.
Lastly, run the command 'sp_recompile'.

After performing all the above steps, if you are unable to resolve the issue and Enterprise Manager still shows the database as 'SUSPECT', then try to restore the data from any previously taken valid backup. However, if backup is unavailable, then, you need to opt for any SQL repair utility to repair SQL database and retrieve the data back.

Stellar Phoenix SQL Recovery is an absolutely risk-free utility to perform mdf repair from SQL Server 2008, 2005, 2000 and recover backup all your database components back safely. Moreover, this MS SQL repair software is compatible with almost all Windows OS and is very much simple to be used by any user without much technical knowledge.

Continue Reading

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