Friday, January 29, 2016

DBCC CHECKDB, WITH CHECKSUM & RESTORE VERIFYONLY

SQL Server database contains different commands for different operations but half knowledge about these commands can make a huge trouble for a DBA. So always take the complete knowledge of commands.

Well, I was reading a question about SQL Server database commands: DBCC CHECKDB, WITH CHECKSUM and VERIFY ONLY and then I join these three strong commands together to make a new article for the readers. Some users believe that, they can use WITH CHECKSUM option to take the backup of the SQL Server database while this is again half knowledge. Now, we will see the use of these three commands.

DBCC CHECKDB: It checks the physical and logical integrity of the database objects.

Why we use DBCC CHECKDB: There are two types of tables in the SQL Server database:

  • Disk-based tables
  • Memory optimized tables

User can perform the DBCC CHECKDB operation on the database which contains memory optimized tables but it works on disk based tables. Since DBCC option is not available for memory optimized tables then user should take the backup of the database regularly (depends on the work plan) to prevent database from memory optimized tables.

If your database is corrupted and you are trying to take a backup of it then, you will also get a corrupted backup of your SQL Server database. In this case, you can check the database by WITH CHECKSUM option. When a user successfully create a backup of the database that means there is no corruption in your database. This is one more option to check the corruption.

WITH CHECKSUM: First of all, the permission to perform read and write on the media (If you are not the member of sysadmin) to a user is must otherwise you will get the permission issue.

WITH CHECKSUM option is used to test page checksums that exists on the data file pages and these pages backed up during the backup process. If a bad page checksum is found then the backup process will stop automatically. In an emergency, a user can override this by using WITH CONTINUE_AFTER_ERROR. Completion of the backup process is also indicating that, there are no broken page checksums.

WITH CHECKSUM option during Backup Process: To enable the WITH CHECKSUM option during backup process use the following commends:

BACKUP DATABASE AdventureWorks2012 
TO DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO

Note:  User can also enable this option by using trace flag 3023. If the trace flag 3023 is turned on that means the CHECKSUM option is automatically enable for the backup command. It is also possible by the SSMS. Go to Options page->Reliability-> Perform checksum before writing to media.

If a user wants to disable it then, use WITH NO_CHECKSUM option.

WITH CHECKSUM option during restore process: 

RESTORE DATABASE AdventureWorks2012 
FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
WITH CHECKSUM;
GO

To disable it, Use WITH NO_CHECKSUM option to disable it.

 RESTORE VERIFYONLY: Assume a situation, when a user takes the backup of SQL Server database successfully that means there is no corruption in the database. After taking its backup, if the backup got corrupted then what will a user do? Its answer is RESTORE VERIFYONLY will be useful for user. This command will check whether a database is corrupted or not.

RESTORE VERIFYONLY FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'

Note: Before restoring the database from the backup, please check the database by using this command to save your time.

Conclusion: We have seen the uses of important commands of the SQL Server database. I hope it will make your work easy and help you to make your database corruption free. 
»»  Read More...

Wednesday, January 27, 2016

How to Manage SQL Server Service Account Error

SQL database is full of extraordinary functions and these functions make user’s life easy and more comfortable. Sometimes it’s easy to find errors and corruption but sometimes it’s like a nightmare. Few errors come up with the starting process and greed the SQL server by their interruption. Here, I will discuss an issue related with the SQL Server.

Incorrect password of the service account or account locked disabled

I got the following error when I tried to start MS SQL Server:

Error:

The request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details.




 Reasons for this error:

  • Changing the location of master database
  • Anti-virus can lock the SQL Server
  • Insufficient permission for SQL Server Service account
  • Server side protocols issue
  • Missing important files due to disk failure or accidental deletion
  • Changing the Password of the service account but not updated on the Server where SQL instance installed
  • SQL Server is disabled from the Service Control Manager
  • Incorrect location on the startup parameters etc


So, these are the few responsible cases for this error.

At the time of startup error, try to open the SQL Server by command prompt and I do so. Simply type,net startmssqlserver to start the SQL Server.One more error message that you may get after executing this command.

“A service specific error occurred”

Check the SQL server error log or the Windows Application Event Log to find the main issue for this problem. Personally, I prefer the SQL Server error log which is a text file and I can see all the error messages easily. To know the location of error log file, go to the SQL Server Service in the SQL Server configuration Manager and check the Starting Parameters.



I highlighted the location of the error log in the above figure and it is default location of the error log file. With this, you can easily check the current location of the master.mdf and masterlog.ldf files.

If the SQL Server starts at least once before failure then it is easy to find the reason of failure by the error log report. In some cases, the error log will not show any entry and these cases are:

Invalid password of Service Account or Account locked
Incorrect location of the ERRORLOG file or the permission issue of the SQL account

In the first case, The SQL Server won’t start so there is no chance for any entry in the log file. In the second case, the SQL Server starts but you cannot find the log entry in the log file. To check this issue, examine the Windows Event Log. Go to administrative Tools-> Event Viewer and filter for errors.



Check the main reason for this problem. I got the following reason:

Logon failure: unknown user name or bad password.

From the above error, it is clear that an incorrect password causing the error. I updated the password of SQL Server account and fix it.

Other possible errors are:
Logon failure: account currently disabled.
Login failed: Account locked out.

To enable the account, go to your database by using SSMS. Security->Logins->sa. Right click on the sa and go to the properties. Now select the Status from the left pane. Enable the login status and click on OK button.



You can also use the script to enable sa account:

  1. Use [master]
  2. GO
  3. ALTER LOGIN [sa] WITH PASSWORD=’ZVGYT@Iu*60i’
  4. GO
  5. ALTER LOGIN [sa] ENABLE
  6. GO

Note: enter the correct password. It is just an example.

The best way to recover SQL server password is to contact with the concern person (Example: DBA). If you are unable to contact the related person so in this case, you can try a professional tool to recover SQL password. This is also a way to recover your database.

Conclusion: So this is a very common problem with SQL Server database. If you are a DBA and have changed any password of the SQL account then always save on a safe place. If needed then share it with concern person. 
»»  Read More...

Wednesday, December 30, 2015

Fix Error – SQL Server does not exist or access denied

Being as an SQL Server user, I always try new methods to fix the SQL Server database errors. Sometimes I become successful and sometimes failed but, I never quit.I am discussing a scenario through this blog which is based on this error.

I got an email from a guy and he shared his problem with me. He was using SQL Server 2008 getting the following error message on the screen:

“SQL Server does not exist or access denied”.

He searched the solution over the internet and checked the all the protocol which can be the reason of this error but he was unable to fix the error. Sometimes user gets the error even if the Named Pipes and TCP/IP protocols are enabled. To fix this error, stay on this blog.

Step 1: Check the status of the protocol name.


Then you need to create the alias on the client side.

Step 2: Searching for cliconfig.exe which is present in the System32 folder and click on it.


Step 3: To create TCP/IP alias, select TCP/IP from the list and click on the Enable button. 


Now it will be add on the Enabled protocols by order section.

Note: Make sure there is no Named Pipe in the list. If any Named Pipes is in the list then disable it.

Step 4: Now click on the Alias section.


Step 5: Click on the Add button.


Step 6: In Server alias section, fill the Server name of SQL Server database.


Step 7: Select the TCP/IP section which is the second option in the Network libraries.

Now fill your SQL Server IP address in the Server name box as shown in the figure.


Click on the OK button.

Note: Default port number of the SQL Server is 1433.

Step 8: Click on the Network Libraries to check the entry.


Now you can connect to remote SQL Server using Server Name.

Other Scenario 

SQL Server 2005: In SQL Server 2005, server assigned the instance name to measure the connection information.

Solution: Just remember one thing; the instance name is also included with the Server name. Take a look on the below image:


You can see the Server Name is included with the instance name P6Instance.

Note: When you select the default instance and the default instance is already present on your machine then, it will be automatically upgraded by the SQL Server setup. A single machine can host only a single default instance. 

Conclusion: These are the two scenarios to fix this error. Always check the network protocol status and follow these steps to fix this error. 
»»  Read More...

Wednesday, December 16, 2015

SQL Database Error 3417 – Steps to Solve This Error

SQL server users may face errors in the SQL Server database whether; they are experts or newbie. If you are a DBA of your company then you have lots of responsibilities and a small error can mess up your whole work. Some SQL database error self-explanatory and if you understand the message correctly then, half of the problem will be solved. In this blog, we will discuss the SQL database error 3417 which is a very common error that appears when DBA starts the SQL Server database.I also got this error message earlier and I am discussing here the steps that I followed to fix it. The error message looks like:


In the above error message, just read this sentence: For more information, review the System Event Log. I checked the event log and it was looking like this:

The file “C:\path of .mdf file” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

Now, I follow the following steps to fix this issue:
  • Go to the data folder where .mdf file is located. Please confirm both the file (.mdf and .ldf) are not compressed.
  • Now, select the Properties.
  • Click on the Advanced button.

  • Un-check the Compress Contents to save disk spaces option.

  • Click on the OK button.
  • Start the SQL Server from the service manager.
This process solved the issue. So always read the error message and error log carefully because it shows the solution.

Other reasons: If you are unable to fix this problem by the above solution then please check the Network Service permission in the data folder.
  • Go to the Data folder and click on the Security/Permission setting.
  • In the Network Service Account, add the Network Service account.

Note: Take the backup of the .mdf file and .ldf file and replace them. If the paths are different (like 32 bit vs 64 bit) then, in this case the SQL Server may give the error. 

If still not able to fix this problem that means, there is a corruption in your .mdf file. In this case, you can try the Stellar Phoenix SQL Database Repair tool to fix thr error. This tool is able to repair corrupt .mdf files without any alteration in the original database.
»»  Read More...

Friday, December 11, 2015

MS Access Database File Formats: ACCDB vs MDB

MS Access 2007 releases with the new file format .accdb and the previous versions has .mdb file format. Every new version comes up with few changes and these changes were visible in MS Access 2007 and the later versions. In this blog, we will see the benefits of .accdb file over the .mdb file. Let’s discuss the benefits of the .accdb file over the .mdb file.


By using the .accdb file you will be able to:

  • Multivalued Fields: This field allows the user to select one or more options for the field values and this process is done by the checkbox. It is same as the different options on clothing like Small, Medium, Large and Extra Large etc.
  • Attachments: The ACCDB file format allows storing the file attachments and other BLOBs (Binary Large Objects) in database fields. 
  • Improved Encryption: By this feature, you can choose the database password and make the database content encrypted. MS Access database uses the Windows Crypto API for the encryption.
  • Safe and Secure Integration with Outlook and SharePoint: Outlook and SharePoint do not allow .mdb file databases due to the security issues. In.accdb file format, these issues are solved properly and allow the validation for the security. 

Disadvantage in .accdb file format:
  • Replication: The .accdb file format does not support the replication. 
  • User-Level Security: This feature is not supported by the .accdb file but, use the encryption option which makes your data unreadable by tools and other programs.
  • Design and Modification Work: All the versions which supports .accdb file format are not handy for the design purpose.
How can I convert the .mdb file to. accdb?

To convert the .mdb file to .accdb file, follow these steps:
  • Open the Database.
  • Click on the File tab.
  • Click on the Open option and select the database which you want to convert.
  • Again, click on the File tab.
  • Select the Save Database As option under File Types.
  • Select the .mdb file format and click on the Save As button.
  • Select the location to save the file.Click on the Save button.




How can I convert the .accdb file to .mdb?

This is the basic question which comes in the user’s mind. Well, to know this process just read the following steps:

  • First of all, close all the opened objects of the database.
  • Click on the File tab then go to the Save As option.
  • Now you can convert into Access 2002-2003 or Access 2000 database. Both support .mdb file format.
  • Now click on the Save As button.

Note:  You can change the name of the .mdb file and the location.


Conclusion: We have seen the important difference between .accdb and .mdb files and need of these formats. Well, this is about the difference but, users choose them according to their requirement.
»»  Read More...

Thursday, November 26, 2015

Key Points to Block SQL Server Attacks

I was solving the SQL Server errors through the forums then I saw a discussion about the SQL attacks. That discussion was very good and it has given me the idea to write a blog on it. Well, this blog is about the easy techniques to prevent the SQL database from the corruption.

When someone talks about the SQL attack then, the first thing that comes in the mind is “SQL injection” and this is very popular known code injection technique to destroy the database. This attack is based on 1=1 is always true principle.Read more about SQL injection here. So, get ready to know about the few simple techniques to make your SQL Server database safe and secure.


  • Do not use dynamic SQL: use parameterized queries or stored procedures, prepared statements instead the normal statement whenever possible.
  • Update and Patch: In the specious situation just apply the patches and updates on the SQL Server database. 
  • Firewall: Always on the firewall to prevent the data from the malicious attack. A WAF (Web Application Firewall) is useful to provide the security before patch file is available.
  • Minimize your attack surface: Some options like xp_cmdshell become helpful for the hackers so try to avoid these options.
  • Encrypt the data: Never try to store the data in the plain text format. Manage the database with the tough password so that the hacker cannot dump the database and steal the information. 
  • Password Security: Don’t forget the basics i.e. change the password of the application accounts into database regularly. If you are unable to do it on the daily basis then change on the alternate days.
  • Understand the application: Just understand the behavior of the web application so you can easily identify when the application is behaving abnormally like using unusual inputs and executing many more database lookups (Attempt to retrieve data). 
  • Use validations: Always use validations on the input fields to prevent the database from the attack.

Always take care of these points to prevent the data from the corruption. If the corruption level is major and hard to handle then use Stellar Phoenix SQL Database Repair tool to repair the corrupt database. 
»»  Read More...

Sunday, November 22, 2015

Tips for Using Triggers in SQL Server Database

Triggers are most important part of the SQL Server Database. Triggers are the special stored procedures which are used to perform actions on the database. These actions have different categories like: DDL, DML and logon etc.

Tip 1: Find the list of triggers using sp_MSforeachtable

If you want to see the list of the triggers along with the schema then, you can use the sp_MSforeachtable to get it. Use this stored procedure like this:

USE AdventureWorks2012
GO
EXEC sp_MSforeachtable
@command1 = 'sp_helptrigger ''?''' ,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';

After executing it, you will get the list of the triggers as the output. See the below image which shows the out of the above query:


In the above output, you can see the different columns with the details like: trigger name, owner, operations and schema.  
  
Tip 2: Find the list of enabled and disabled triggers

Every database has tables and if the user creates any trigger then it comes up under table section. A User has options to enable and disable triggers by clicking on it or by the query. Just right click on the trigger which you want to enable or disable it and it will look like this:


To enabled or disabled the trigger using query, just execute the below queries.

Disable the trigger

USE Database_name
GO
DISABLE TRIGGER Trigger_Name ON Table_Name
GO

Enable the trigger:

USE Database_name
GO
ENABLE TRIGGER Trigger_Name ON Table_Name
GO

To see the status of all the triggers, simply run the following query:
Select * from sys.triggers

In this case, the user will get the status of all triggers presented in the database.


In the above image, the red rectangular box showing the status of the trigger. In this section, the output always presents in the binary form (0 or 1). In the above image the trigger “Trg_InsertSalePerson” is disabled because its binary value is 1 in the “is_disable” column. To check its result, now enable the same trigger and you will get this message on the screen:


Now run the same query again
Select * from sys.triggers
At this time, the output will show zeros for both the triggers:


We have one more option to see the status of the triggers. Execute this query 

Select TBL.name AS TableName,
Schema_name(TBL.schema.id) AS Table_SchemaName,
TRG.name AS TriggerName,
TRG.parent_class_desc,
CASE
WHEN TRG.is_disabled = 0 THEN ‘Enable’
ELSE ‘Disable’
END AS TRG_Status 
From sys.triggers TRG
INNER JOIN sys.tables TBL
ON TBL.OBJECT_ID = TRG.parent_id
AND is_disabled = 0

The output will be:


Now, the user can see the difference TRG_Status and both are enabled. So these are the simple tips about to find the status of triggers. 
»»  Read More...

Thursday, October 29, 2015

Storage Engines in MySQL

MySQL is an open-source RDBMS (Relational Database Management System) which is widely used RDBMS after Oracle. It supports different storage engines and these storage engines have their own properties. In this article, we will discuss few storage engines of MySQL.

InnoDB Storage Engine: It is default storage engine for MySQL 5.5 and later versions. File formats of this storage engine are .idb, .frm and .ibdata. InnoDB provides the compliant transaction function of ACID, along with foreign key support. It has the ability to maintain its own buffer pool for indexing in the main memory and data caching. Main goals of InnoDB storage engine are Portability, data security, OLTP orientation, performance, reliability, and scalability etc.

Key functional characteristics:

  • Auto recovery from crash
  • Efficient IO
  • Full transactional support (COMMIT and ROLLBACK)
  • MVCC (Multiversion Concurrency Control)
  • Row-level locking
  • Row data stored in the primary key format
  • User can freely mix the InnoDB tables from other storage engines

Disadvantage of InnoDB Storage Engine

  • It Doesn’t support Hash Indexes and Full-text search indexes
  • No cluster database support
  • It is not available in classic edition
The Architecture of InnoDB Storage Engine: InnoDB architecture is well-suited to modern, online transactional applications as well as embedded applications. Architecture of the InnoDB storage engine is shown in the below:



MyISAM Storage Engine: It was the default storage engine for the prior 5.5 version for MySQL database. MySQL uses the .frm file to store the definition of the tables so this is also supported by MyISAM. The main file extensions for this storage engine are.MYD (MYData) and .MYI (MYIndex). MyISAM is the improved version of MySQL, ISAM. After MySQL 3.23, MyISAM replaced ISAM as the default storage engine.

Advantages of MyISAM Storage Engine:
  • Simplicity: It is very easy to use especially for a newbie. 
  • Speed: It is very fast under the normal condition.
  • Full-text Searching
Disadvantages of MyISAM Storage Engine: 
  • It does not support transaction or foreign key constraints.
  • It has a poor crash recovery which can be fixed using REPAIR TABLE, but it needs an additional administrative overhead. 
  • It uses table locking feature. In this feature, when a row is inserted or updated then the other modification on the table helps up until that particular request has been completed.
  • No order for the data storage
Falcon Storage Engine: Falcon storage engine was specially developed for MySQL RDBMS. When Oracle purchased MySQL then its development was stopped. The main feature of Falcon is transactions.

Advantages of Falcon Storage Engine:
  • It supports COMMIT/ROLLBACK
  • Row level concurrency by MVCC
  • Non-blocking read & crash recovery
  • Fine Grained multi-threading
  • Use threads and processor for the data migration
  • Support web application
Disadvantages of Falcon Storage Engine:
  • It does not use clustered index architecture 
  • It does not support “Dirty” reads isolation level
  • It does not support statement based replication
MySQL Federated Storage Engine: This storage engine gives the facility to create the table form representation of foreign (Remote) tables.

Advantages of MySQL Federated Storage Engine:
  • Useful for ad-hoc queries
  • Server side replication filtering
  • Useful for replicate the triggers actions from the other non-replicated database
Disadvantages of MySQL Federated Storage Engine:
  • The remote server must be a MySQL server
  • No support for transactions
  • Federated tables do not support indexes
  • It does not support HANDLER (It provides direct access to table storage engine interface)
»»  Read More...

Friday, October 23, 2015

How to Know the Recovery Model of Your Database

Recovery model plays an important role in SQL Server because they decide the nature of your transaction log file and the recovery strategy. SQL Server supports three types of recovery models: Simple, Bulk-Logged, and Full.These SQL Server recovery models have their own properties. User can also change the recovery model after creating the database.Let’s see brief information about them:

Simple Recovery model: If your database is in simple recovery model then following operations cannot be performed on database-

  • Database Mirroring
  • Log Shipping
  • AlwaysOn feature
  • Point-in-Time recovery

You can use simple recovery model when:

  • There is no need for point-in-time recovery
  • Data can be derived and easily recreated
  • Data is static

Full Recovery Model: A user can choose the full recovery model:

  • For point-in-time data recovery
  • When data became very important and user cannot lose the data
  • For the use of advanced features like log shipping, mirroring, AlwaysOn etc.

Bulk-Logged Model: A bulk-logged recoverymodel is used to preserve the transaction log records after full backup of the database.

  • Data is critical, but user doesn’t want to log large bulk operations
  • Bulk operations complete at different times versus normal processing

Different type of backups that you can run on the bulk-logged model:

  • Copy-Only Backup
  • Differential Backup
  • Complete Backup
  • Partial Backup
  • File/Filegroup backup
  • Transactional log backup

After the small overview on recovery model, here we will know about multiple ways for checking the recovery models of SQL Server database:

Step 1: Using SSMS (SQL Server Management Studio)
Go to Object Explorer and Right Click on your Database-> Go to Properties ->Options ->Recovery Model


Step2: Using catalog View
User can also check the recovery model type by the following query:

Select name, recovery_model_desc FROM sys.databases
GO

The result has two columns, first is name of the database and second is recovery model type as shown in the figure:

Step3: Using Metadata Function:
DATABASEPROPERTYEX() is a metadata function to find the recovery model of the database. This function returns only one database at a time. Here is the query:

SELECT DATABASEPROPERTYEX (‘DatabaseName’,’RECOVERY’) “Recovery Mode1”
GO

The output will be:





Step 4: Using sp_helpdb
The user can also use the stored procedure sp_helpdb to check the recovery model of the databases.

EXEC sp_helpdb
GO

The output contains several fields and in the Statussection, a user can easily find the recovery model type.

Step 5: Using Object Explorer
In the object explorer, click on the Databasesand press F7. The output will be like this:




So these are the basic options to find the recovery model type of the database. 

Final Words: We have seen the different reasons to choose the recovery models and all the steps to find the recovery model of the database. When a user switches one recovery model to another recovery model then, he/she should be aware of its important points. Every recovery model has its own features and all are essential so choose your recovery model very carefully to save your database. 
»»  Read More...

Sunday, October 4, 2015

Repair Clustered and Non-Clustered Index in SQL Server

In a general way, if we define a cluster then we will define like this: A cluster is a group of some items which acts like a single system. From the figure 1, you can easily find your answer. We have 3 clusters on the image. They all have their own systems but, for each other they are only one system.


Clustered Index: Every database has rows and columns. Clustered index is used to determine the order in which the rows of the table are stored into disk.Let’s take an example, suppose we have table named Student which has a column named Roll_No. If we create a clustered index on the column Roll_No then all the rows inside the table Student will be sorted on the disk space according to the column Roll_No.

Point to remember: Index follows the tree data structure in which leaf nodes contains the actual data of the table. Leaf nodes are those nodes which have no child and they will always present on the bottom level of the tree.


Advantage: Searching and fetching of the data become faster with the cluster index because data will physically sorted right next to the each other.

Disadvantage: Clustered index is not useful or those columns that have repeated value.

How to crate clustered index: I will discuss a T-SQL method to create clustered index. Let’s take the same example of table Student that we have discussed above. With a default database, Clustered index automatically created with the primary key.

USE AdventureWorks2012;
Go
CREATE TABLE dbo.Student
(Roll_Noint NOT NULL,
Name char(10) NULL,
Address varchar(70) NULL);
GO

This query will create a table of three columns named Roll_No, Name and Address. Now we create a clustered index:

CREATE CLUSTERED INDEX IX_Student _Roll_No
ON dbo.Student (Roll_No); 
GO

This is the way to create clustered index in a table. You can choose the clustered index name according to your choice. Rather than this approach, you try it by SQL Server Management Studio.

Non-Clustered Index: Non-clustered index contain pointers that point to actual data. Pointer is a variable which contains the address of actual data.


From the above image it is clear that, the leaf nodes contain all the pointers. The main difference between clustered and non-clustered, in clustered type when we reach on the leaf nodes then we are on the actual data. In non-clustered type, when we reach on the leaf nodes then we are on the pointers.

Advantages: This approach is useful when data has redundancy.

Disadvantages: Data fetching is less easyas compared to clustered index. 

Note: A table can contain more than one non-clustered index because they do not affect the order of the row which contains data.

How to crate Non-clustered index: Using T-SQL, you can create a non-clustered index like this:

USE AdventureWorks2012;
GO
CREATE NONCLUSTERED INDEX IX_Student _Name
ON dbo.Student (Name); 
GO

Corruption in clustered and non-clustered index: When corruption occurs in the SQL server database then you should check the corrupt objects. Run the DBCC CHECKDB command:

DBCC CHECKDB(CorruptDB) With No_InfoMsgs, All_ErrorMsgs, TableResults;
GO

It returns database ID, object ID and index ID. Here the term index ID shows the status of the index. Take a look on the index ID mapping:

ID 0 = heap
ID 1 = Clustered
ID 2 = Non-clustered 

Solution: If the index ID is greater than 1 that is non-clustered index then drop and recreate it. If the index Id is 0 or 1 then try these methods:

Restore from the backup:

If the data model is full or bulk logged then you can backup of the tail log. In simple recovery model, you will not get this option and you can only restore from the last backup.Run this command to restore from the backup:

RESTORE DATABASE YourDatabaseName FROM DISK = ‘C:\YourDatabaseName.bak’ WITH NORECOVERY

If you want to repair a specific page then you can run this command:

RESTORE DATABASE YourDatabaseName PAGE = ‘1:85663’ FROM DISK = ‘C:\YourDatabaseName.bak’ WITH NORECOVERY

In section PAGE = ‘1:85663’, 1 is Index ID and 85663 is the page no.

More options:

You can also try these automatic repairing options. Take a look on these options:

REPAIR_REBUILD: Its syntax is:

DBCC CHECKDB (‘YourDatabaseName’,REPAIR_REBUILD)

Basically this is for minor corruption on the database.

REPAIR_ALLOW_DATA_LOSS: As it is clear from its name, it allows the data loss and tried to repair all errors. When corruption is very severe then this command deallocates the page from the memory and modifies the links. Its syntax is:

DBCC CHECKDB(‘YourDatabaseName', REPAIR_ALLOW_DATA_LOSS)

A Final Option: If you are unable to repair your database by these options then the final way is to use a secure third party tool. Before buying any tool just read all instructions very carefully.

Final words: We have seen the different methods to repair the corruption of index in SQL server database and I am sure that, you can use these options without any trouble and according to level of corruption. DBCC CHECKDB is very strong tool but it can turn into a dangerous tool when someone usesthis without knowledge.
»»  Read More...