Feed: Stellar SQL Database Recovery - AggScore: 45.4

Explaining corruption in GAM, SGAM and IAM pages and Real-time Benefits of Advanced SQL Database Recovery Utilities

I was running SQL Server 2000 on my home PC with Windows XP installed. My database tables never showed any sort of unusual behavior until I ran a query yesterday to count the number of rows in one of my primary tables. I got the below error on my screen:

‘Attempt to fetch logical page (1:42724) in database ‘test’ belongs to object ‘report-table’, not to object ‘RRR’.Connection Broken.‘

I tried to fix this by running DBCC CHECKDB with the ‘repair_rebuild’ option. This command resulted in one more error as shown below:

‘Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.‘

I made another try, this time using DBCC DBREINDEX to rebuild indexes but could not get away with these errors.

The error ’8905′ was more frequent. For the same reason, I ended up collecting information about this from a couple of resources on the web. A GAM page is responsible for mapping a specific file interval that is usually 4 GB in size. It contains a corresponding bit for each extent in this file interval. The bit has a value ’0′ if the extent id allocated or ’1′ when it is completely unallocated. An SGAM page differs from a GAM page by the following two ways:

• A set bit corresponds to a specific extent, wherein each page is assigned to an IAM Page for various indexes.
• The extent contains at least one allocated and one unallocated page.

An IAM page is also very similar to these pages, except that every extent has a dedicated bit that is allocated to the index associated with the IAM page.

The error suggested that one extent was shown as allocated in GAM, while the SGAM and IAM pages didn’t reflect the same. I examined the SQL Server error log and the Windows application log to see if there was a hardware failure. As my backup was also incomplete, a full database restore wasn’t feasible.

At the end of the tunnel, I switched to commercial third-party tools for some hope of getting back my lost data. It appeared to me that these SQL recovery utilities are one of the best solutions in the market for dealing with such cases of severe corruption. They helped me to recover almost everything from my damaged SQL database, such as inaccessible tables, views, keys, indexes, etc.

Resolving Critical Case of Index Corruption in MS SQL Server 2000

I am presently using SQL Server 2000. I have a database containing a clustered table named ‘dbo.MyWidgetTable’. This table has a unique constraint on it as follows:

‘CONSTRAINT UK_Widget_UID UNIQUE NONCLUSTERED (Widget_UID, WidgetColor)‘

This constraint is applied to help ensure referential integrity when dealing with foreign key relationships. There is one more clustered index on the same table. Further, the table also has two non-clustered indexes as follows:

CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable
(Widget_UID, WidgetColor, CreatedAt)

CREATE NONCLUSTERED INDEX IX_CreatedAt ON dbo.MyWidgetTable
(Widget_UID, WidgetColor, WidgetStatusFID)

Clearly, the non-clustered indexes are overlapping the unique constraint. Now, when I try to make changes to the unique non-clustered index to have key columns ‘CreatedAt’, ‘WidgetStatusFID’ and then add the following constraint:

‘ALTER TABLE [dbo].[MyWidgetStatusTable] WITH NOCHECK ADD CONSTRAINT
[FK_MyWidgetStatusTable_MyWidgetTable] FOREIGN KEY([WidgetFID], [WidgetColor])
REFERENCES [dbo].[MyWidgetTable] ([Widget_UID], [WidgetColor])’

I come across the following error:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.
MyWidgetTable’ that match the referencing column list in the foreign key
‘FK_MyWidgetStatusTable_MyWidgetTable’.

I checked the table to confirm if the primary key constraint has been removed. But the table had a primary key in place. Next, I verified the SQL Server error log and found that the below message was recorded to the file:

‘Could not find the index entry for RID ’160314a879010000′ in index page (1:428288), index ID 3, database ‘DATABASENAME’.‘

I tried to search the error on the web. A few resources indicated that this is the case of SQL index corruption. First off, I opened the SQL Server Management Studio and then ran the DBCC CHECKDB command as follows:

‘DBCC CHECKDB (‘DatabaseName’) WITH ALL_ERRORMSGS‘

This gave no errors after complete execution. Then, I ran the ‘DBCC DBREINDEX’ command with the following syntax:

‘DBCC DBREINDEX (@TableName,’ ‘,90)‘

I ran this command for each table that had the non-clustered index. This time I found errors with DBCC CHECKDB.

In order to avoid data loss due to corruption, I immediately rushed to a database recovery expert. He advised me to go for advanced SQL recovery software. This was possibly a heck of a task for me to decide on which one should I prefer. There were abundant utilities over the Internet that promised a complete database recovery. Fortunately, I ended up using one of the finest tools, ‘Stellar Phoenix SQL Database Recovery’. This software helped me to restore all the database objects easily to a new SQL database. I imported the newly created database and it worked without any problems. Furthermore, it supports MS SQL Server 2008 R2, 2008 7.0, 2005 and 2000.

How to Easily Plan Out SQL Recovery and Bring Your Database Back Online

For the last couple of years, all our backend SQL databases were running fine. Recently, an error showed up on accessing one of the primary databases. This was error 823 that read ‘I/O error (bad page id) detected during read at offset’. This was some kind of corruption because of which the database failed to open. As the database had entries and records of the past two years, I was desperate for a quick recovery. I took the advice of two DBAs and followed this procedure to get back all the lost objects intact:

• At first, detached the database from the Enterprise Manager and saved the MDF file to a safe location.
• Created a new database having the same name and at the same location where the original one was kept.
• Now, stopped the SQL Server service and replaced the database files (both MDF and LDF file) of the newly created SQL database with the database files of the corrupt database.
• Restarted the service and then launched Enterprise Manager.
• The database was now in suspect mode.
• Next, launched the Query Analyzer and executed the below given commands on the master database:

‘sp_configure ‘allow updates’, 1

go’

‘reconfigure with override

go’

• Now, it was easy to modify the system catalog tables.
• To verify the database status, I issued the following command:

‘select status from sysdatabases where name=’your database name’ (replace with your database name)’

• Next, executed this:

‘update sysdatabases
set status = 32768 where name=’your database name’’

• Restarted the SQL Server service to automatically switch to the emergency mode.
• Created a recovery database named ‘dbrecover’ and moved all data in the damaged database to the new recovery database through DTS (Data Transformation Services).
• For rebuilding the log, used DBCC as follows:

‘DBCC rebuild_log (‘your database name’, ‘new log filename with path’)’

• Next, executed the following series of commands on the database:

‘use master

go’

‘sp_dboption ‘your database name’,‘single_user’,’true’

go’

‘DBCC checkdb(‘your database name’,repair_allow_data_loss)

go’

• Changed the database status back to normal using:

‘use master

go’

‘update sysdatabases

set status=0 where name=’your database name’

go’

• Now I got the database back online.

However, this was complex, we recovered almost all of our data from the troubled database. But some entries were still missing from important tables. The primary reason for this was running CHECKDB with ‘repair_allow_data_loss’ option. For doing complete SQL database recovery, ultimately we had to resort to commercial third-party tools. These utilities were worth the price. They helped us quickly scan the database and repair the corrupt MDF file with ease. With the help of these software, I was able to save some queries, procedures, etc. in a separate text file.

IAM Page Corruption Leading to SQL Database Inaccessibility and Recovery Solution

If you are using Microsoft SQL Server as the back-end RDBMS for your applications, you need to have some idea over its internal architectures. This will help understanding messages logged in the event log and resolve problems efficiently. However, if you encounter a problem, which has made your precious SQL Server database completely inaccessible and you are not finding any inbuilt way out, you can always rely SQL recovery software, like Stellar Phoenix SQL Recovery software.

Sometimes, users of MS SQL Server 2000 encounter a special kind of trouble in which the entire database becomes inaccessible due to problems in the IAM pages. In such cases, the error message flashed by the SQL Server database can be written as below:

“Table error: Could not check object ID ‘XXXX’, index ID ‘YYYY’ due to invalid allocation (IAM) page(s).”

From the error message, it can be guessed that there is some problem with the IAM pages. Now, what exactly these IAM pages are? As you may know, each data page of MS SQL contains 8 KB of information, which is the data in the rows. A group containing 8 such adjacent pages makes an extent and above this there are heaps, which contain a huge pool of data pages. The structure is such that the SQL Server data pages and the rows in them are neither in any order nor linked together. Here, the IAM page contains the information about the connection between data pages. Thus, these IAM pages or Index Allocation Map pages are essential to navigate between heaps and access data in the rows.

As in the above error message, one of the IAM pages has got corrupted; the data in the SQL Server database has become completely inaccessible.

The widely suggested and first-hand solution for the problem is to run the ‘DBCC CHECKDB’ command. This command has actually tremendous potential to repair the corrupt SQL Server database and recover the data. But, in certain circumstances, if the database has been attained severe damage, the ‘DBCC CHECKDB’ command even fails to respond.

Still, if you have a valid backup of your database, you can easily restore the database and regain access to your precious data. Even if you do not have any valid or recent backup, you can recover your SQL Server database with the help of any reliable SQL data recovery software.

As I have suggested, Stellar Phoenix SQL Recovery software is highly appreciated SQL recovery software to recover lost or inaccessible data from almost all instance of database problems. The software is completely risk-free in its data recovery approach and with interactive user interfaces, immensely easy-to-use.

Advance utilities to recover data from severely corrupt SQL Server 2005 database

SQL Server is a relational database application developed by Microsoft. For its increased scalability, reduced downtime, good performance and tight yet flexible security controls, SQL Server database is popularly used at the back-end of many applications with huge on-line transaction load. In the course of their working, these applications store many large sized binary objects on the database, which can even be of some MBs. SQL Server stores these large application objects with LOB (Large Objects) data types. But, at times due to any application error or anything similar, the LOB page structure gets corrupt and all the LOB data on the database becomes completely inaccessible. In such situations, you need to take the help of any advanced SQL Server recovery software to recover the data back.

The LOB data type in your SQL Server database usually includes DBCLOB (Double-byte Character LOB), CLOB (Character LOB) and BLOB (Binary LOB). If the LOB page structure of the database gets corrupt, all the data with DBCLOB, CLOB and BLOB data types become inaccessible. In such cases, if you are using SQL Server 2005, you may come across the below error message on your screen:

“Msg 7105, Level 22, State 6, Line 1
The Database ID 11, Page (255:177), slot 1 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE”

Causes: There can be a number of possible reasons behind the above problem. Some of the prominent ones include – LOB page structure corruption, SQL Server engine error etc. Apart from this, in some occasions while accessing the data from the SQL Server database, the query you execute uses the ‘READ UNCOMMITTED ISOLATION LEVEL’ or ‘NOLOCK’ query hint, which leads to the problem.

Resolution: In order to resolve the problem, you need to run the ‘DBCC CHECKDB’ command on the troubled SQL Server database. ‘DBCC CHECKDB’ command is to find out the exact cause behind the error and repair the same.

But, in case of severe damage or corruption in the SQL Server database, if ‘DBCC CHECKDB fails, you need to look for any third party SQL recovery software to the job. Stellar Phoenix SQL Recovery is a highly advanced database recovery software to repair the corrupt SQL Server database and recover the inaccessible data back. The software is completely read only in all its recovery process and with interactive graphical user interfaces, pretty easy to use.

Analyzing the risk of database corruption due to fragmented sparse files when running SQL Server on Windows Vista

One major problem that I faced while running SQL Server on Windows Vista was sparse files. This problem was concerning disk fragmentation. As the database files are usually large in size and heavily fragmented, I used to get various errors from the operating system. These errors showed up every time I performed some of the major database operations. Frequently, I saw the Error 665 on my screen that stated:

“The requested operation could not be completed due to a file system limitation”

I remember when I was using Windows 2003 R2 earlier, I used to encounter Error 1450:

Unveiling some myths around SQL database corruption and performing recovery using professional third-party software

There are a plethora of misconceptions that surround SQL Server database corruption and recovery. Most users still believe that they can easily get away with all the issues concerning SQL corruption by just performing a simple server restart. But, the truth lies in the fact that neither restarting the SQL Server nor rebooting the Windows computer can fix a corruption. In order to properly address the issue of corruption, you need to restore or rebuild the corrupt page.

When your database has been marked as ‘SUSPECT’ by the SQL Server or is in a ‘RECOVERY_PENDING’ state, it is advised not to detach and re-attach the corrupt database. If you cannot run crash recovery on your database, the attach would surely fail. The following are some of the interesting facts about how a rebooting process can help in resolving minor database corruptions:

• If you have a damaged page image in the memory, whereas the on-disk image of that page is consistent, you might be able to fix this by rebooting.
• If you performed some actions during the reboot that made the page to no longer be allocated, you will be able to resolve the issue by a simple reboot.
• If an I/O operation failed because of rebooting of the I/O subsystem, you can easily fix the issue by rebooting your computer.

The DBCC CHECKDB utility will help to run consistency checks on your damaged database and resolve most cases of SQL corruption. However, users tend to perform backup with ‘CHECKSUM’. They think of it as another repair option that would eliminate the need to run DBCC CHECKDB. But, this doesn’t hold true in every case. It involves a high risk of causing in-memory corruptions. If you have a bad memory chip that damages one of your SQL Server data files, then the corrupt page will still carry a valid checksum. This would be ultimately recorded to your disk until you run DBCC CHECKDB to identify and fix the issue.

The best possible way to get rid of corruption and avoid any case of serious data loss is to opt for advanced SQL database recovery software. These third-party tools are embedded with robust, indigenous algorithms to scan the damaged SQL database and recover all valuable objects, such as tables, views, indexes, stored procedures, constraints, etc. These software can safely repair corrupt database files created in MS SQL Server 2008, 7.0, 2005 and 2000.

Finding and troubleshooting database corruption in MS SQL Server 2000

The most important concern for a database administrator is disaster prevention and recovery. Their main job is to ensure high data availability to the users. However, database corruption may sneak in at any time and cause a catastrophic situation of data loss. When coming down to SQL database corruption, it is more specifically related to issues at the IO subsystem level. Every time a corruption is encountered in your SQL database, it is generally due to problems with one of your drives, controllers, or drivers. As a consequence, you may experience downtime while performing some of the important database tasks and receive a series of errors that indicate damaged pages in the database. To effectively overcome this problem, you should go for SQL recovery through a reliable third-party tool.

As a practical example, consider this case. You may come across the following error message on your screen while using your MS SQL Server 2000 database:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in database ID 13 at offset 0x0000002229a000 in file ‘D:DevelopDatabasesBroken1.mdf’

The problem may get worse when the corruption is not identified at the right time. At this stage, it becomes important how you fight this corruption. You should not immediately detach and reattach your database or restart the SQL service. Moreover, you should not directly jump to one of the repair options of the CheckDB tool, as this should be done as the last resort.

Instead, you should run an integrity check and try to get to the root cause of the problem. To do this, you need to run CheckDB with the ‘All_ErrorMsgs’ option. This would provide information regarding what is damaged and help you perform SQL Server recovery.

After resolving corruption in your SQL database, you need to eliminate the root cause to ensure that it won’t happen again. This entirely depends on the outcome of the CheckDB tool. However, if you continue to face problems, then you should try to perform MDF repair using advanced third-party software. These tools are proficient in repairing and restoring all the damaged database objects, including tables, views, indexes, stored procedures, etc.

Powerful as well as reliable utilities to repair and recover SQL database objects

Microsoft has produced and marketed many useful products, among which SQL Server is one. This is a relational database application meant to store and manage data, as requested by other applications. SQL Server database is available in 10 different international languages and supports almost all major Windows operating systems. For its wide compatibility and advanced features, many organizations use SQL Server database to store their business critical data in it. But, at times the SQL database gets corrupt and all the important data in it becomes completely inaccessible. However, there are many innovative SQL database recovery software available in the market, which can help you repair the corrupt database and recover inaccessible data.

MS SQL Server has many editions meant for different category of users; from small scale database on a single computer to large scale ones, accessing huge volume of data from the network. For its reliable and advanced internal architecture, Microsoft has used SQL database in many of its other products, like SharePoint Server. But, every application can fail at a point of time and so does SQL Server.

There are a number of reasons for which the SQL Server database becomes corrupt. Below listed some of the prominent ones:

• Unexpected system shut sown
• Virus or malware infection
• Corruption to the file system of the computer
• Operating system corruption etc.

In any of the above situations, you failed to access the SQL Server database. Microsoft has provided certain inbuilt tools to repair the corrupt SQL Server database. ‘DBCC CHECKDB’ is used by many SQL users to repair their damaged database. But, in case of severe corruption, this utility fails to repair the database and all your data remains inaccessible.

In such cases, you need to take the help of any third-party SQL recovery utility. These SQL Server recovery utilities are developed with many powerful algorithms to repair the severely damage SQL database and recover all the inaccessible database objects, including the Tables, Stored Procedures, indexes, Triggers, User Defined Functions etc.

These database recovery utilities are completely read-only and safe to use. Moreover, in case of severe corruption, when recovery of the tables is not at all possible, these utilities help in retrieving the queries in a text file so that you can easily re-create the database.

Stellar Phoenix SQL Recovery Software V4.0 has been released

Stellar data recovery, leaders in data recovery software & services announced the launch of Stellar Phoenix SQL Recovery Software V4.0. Now SQL recovery software will compatible with SQL server 2008 R2 as conformed by Stellar R&D team.

New Added Features: Now Stellar Phoenix SQL Recovery V4.0 will support following features.

• Support for MS SQL Server 2008 R2.
• Support for fast scanning algorithms.
• Support for large SQL Server .ndf files for all SQL server versions.
• Support for XML data types & indexes.
• Support for MS SQL Server 2008 file stream data types, sparse columns & columns set property.
• Support for separate log report after scanning database.

Minimum System Requirements

• Operating system: Windows 2000 / 2003 / XP/ Vista / Windows 2008.
• Hard Disk: At least 50 MB of free disk space.
• RAM: 1 GB minimum (2 GB recommended).
• MS SQL Server: 7.0, 2000, 2005, 2008.