Repair Corrupt SQL Server Database

-->

In this blog post, I will try to explain all possible steps that you will have required to repair your corrupt SQL server database. Let's consider a case where you are working on SQL server database, trying to access some records from a table. During table access process you have got an error message that specifies, you can't access desired table due to database corruption. Corruption in SQL server tables can happen due to several reasons; some most prominent reasons are virus attack, power outage, improper system shutdown, hardware failure etc.

What to Do Now? It is the first question that will come in your mind. I will suggest some tips at this point.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

How to Check Error Message?

Run DBCC CHECKDB command on the database, it will write a reported error message in SQL server error log. The reported error message will show you how many consistency errors are found.

For example: You may get below error message in your error log

2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.

Note: You can run DBCC CHECKDB command on the database any time no matter your database is online or offline but you will get information message only when DBCC CHECKDB will run on online database.

How to Fix Corruption: There are three methods that you can try to repair corrupt table. The methods are....

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

Check Hardware Configuration: First of all check your hardware configuration. If you are not a hardware geek then connect with your system support team and fix the issue.

Restore from Backup: If backup of corrupt table is available then restore from updated backup.

Run DBCC CHECKDB with repair_allow_data_loss: DBCC CHECKDB is a free utility offered by Microsoft to repair & recover SQL server database with minimum data loss.

ALTER Database Employee_Details Set Single_User

GO

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;

GO

ALTER Database Employee_Details set multi_user

GO

Read More

Causes & Resolutions for MS SQL Server Error 5120

-->

Are you getting SQL server error 5120, when you try to attach some database files to Microsoft SQL server? This may happen because you have selected a file that is in use by other SQL server instance or selected a wrong file for the attachment. To fix this, firstly you have to check the location and status of SQL server files that you want to attach.

The default location of data & log files are:

C:Program-files\MSSQL\Data\Database-Name_Data1.mdf

C:Program-files\MSSQL\Data\Database-Name_Log1.ldf

Note: Your data & log files may have different location. You can change it according to your need.

The error message that you may got is look like:

Unable to open the physical file "C:Program-files\MSSQL\Data\Database-Name_Data1.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

Possible Cause: As I have mentioned, this error message occurs due to many reasons but it may also occurs when you are not allowed to access the file directories. For the security purpose, database administrator does not allowed you to access the file directories. To access the file, you have to login as administrator or get permission to access the database files.

Resolution: As we know that solution is not rigid for sql database recovery. It may change according to the scenarios or causes. Here, we will discuss two scenarios and its possible solutions.

Solution 1: If you have found access permission problem with the database files then login as a administrator and fix the problem.

Solution 2: If you have found data file is present in C drive & log file is present in E drive then change the location of data or log and placed data and log files in a same drive. Now attach the database and fix it.

Point to Ponder: Generally, solution 1 is enough to solve Microsoft SQL error message 5129 because most of the database administrator (owner) does not allow every administrator to access the database files.

Read More

Discussing ways to easily recover SQL Server 2000 suspect database

-->

MS SQL Server 2000 is widely used all over the world as the data storage component of several large websites. It is a cross-platform application that supports various advanced and user-friendly features, such as Microsoft search service, federated servers, indexed views, etc. It can help you achieve the desired performance levels required for running large web applications. However, an SQL database may occasionally become corrupt. The potential reasons responsible for this corruption are virus infection, hardware errors, abnormal system shutdown, etc. As a consequence, all valuable objects in the database become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

Suppose, you use MS SQL Server 2000. While trying to access one of your databases, you may receive the following error message on your screen:

“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)”

Cause: The above error occurs because you are trying to access a database that is marked as 'suspect'. There could be a number of reasons responsible for this, such as database corruption, missing device driver, database being moved to another location, etc.

This would render your SQL database inaccessible and lead to severe data loss. At this stage, you may use an updated backup to restore the database. However, if the backup is unavailable, you should follow the below mentioned resolution steps.  

Resolution: You can try to resolve the issue by following any of the given methods:

• Make sure that your system has all the necessary device driver files.
• If your SQL database is corrupt, you need to follow the given procedure.

• First, drop and recreate all indexes in your database.
• Create a new database table and copy all records from the original table to this newly created table.
• Now, update the statistics.
• Run the following command:

'DBCC UPDATEUSAGE'.

• Finally, run the 'sp_recompile' procedure

If the problem still persists, you should take help from a competent SQL server recovery tool. These third-party software are proficient in repairing corrupt SQL databases to recover valuable objects, including tables, views, indexes, constraints, triggers, stored procedures, etc. They allow you to take a sneak peek at the database objects prior to recovery.

Read More

How to troubleshoot SQL server Error 9002

-->

SQL server database mainly contains two files, one is data file & another is log file. Data file contains the information about data while log file contains the information about transactions in the database. In simple recovery model, log files are managed automatically but in case of bulk-logged & full recovery model database administrators are responsible to manage the size transaction log file. You can perform this by shrinking the transaction log file.

Sometimes, SQL server database generates an error message 9002 when it found insufficient log space. This error message will appear when SQL server database is online or in recovery mode. If database is online and struggling with log space problem then you can read the database but unable to do any update on it. If log fills during recovery then SQL server marks database in suspect mode. 

Space Full

-->

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

 

Work Around: As error message states, if the size of log file is full then immediately take the backup of database and issue some free space for the log file. Now the point comes in your mind, how to perform this. To do this follow the below given steps:

• Backup the transaction log.
• Issue some disk space.
• Move the log file to a disk drive (secondary storage media) with sufficient space.
• Enlarge the log file.

These are the possible user action required to perform the SQL server recovery. Detail description is given below:

Backup the transaction log: If you are using simple recovery model then backup of transaction log is not possible. Take the backup of transaction log file, if you are using bulk-logged or full recovery model.

Issue some disk space: Regardless of recovery models, freeing the disk space allow recovery system to enlarge the transaction log file automatically.

Move the log file to a disk drive: If you are unable to free some disk space to the transaction log file then move the log files to a disk drive with sufficient space.

Read More

Unable to Start MS SQL Server Database

Database Users or administrators can start Microsoft SQL server database from the task bar. To start, go to the task bar, open sq server service manager and then click on the start button. Sometimes MS SQL server is not start through this method and give some error message like

2011-11-20 10:15:39.87 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

Most possible reason for the above error message is “model is corrupt”. To resolve this, you can create a new copy of tempdb. This will fix the starting problem of SQL server database. If you still face the same problem then first of all make the backup of all MDF & LDF files.

How to Make Backup when Unable to Start SQL Server? As we have discussed, you are unable to start sql server then take a backup of data & log files by below steps:

• Set SQL server service manual by Windows control panel.
• Reboots the windows.
• Copy all the data and log file to the another computer or any removable media.

Attention: Don't try any methods until you have backup.

Have Backup? If Yes then use another copy of sql server with same version and level to restore model database using model.mdf & model.ldf. After restore, detach model database from new sql server and copy the data & log files to replace problem server. It is little bit messy to recover SQL server but not hard. In simple words, we can say that you will have to perform three steps:

        Restore-->Detach-->Copy (data & log files)

Doesn't Have Backup? If you don't have any backup of sql server database then it is recommended you to install a new copy of MS SQL server. When installation is complete the stop the service and copy the Model file to replace problem server.

Summary: SQL server database starting problem can be resolved by two method, one is restore-detach and another is new installation. Restore-detach method is hard in comparison of new installation method but it is effective.

Read More