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...

SQL Server Database Consistency Check Options

We can check SQL Server database integrity of all the objects in the specified database by using DBCC CHECKDB command. It helps to check database corruption. DBCC CHECKDB command offers many options to check sql server database integrity. Check this syntax which covers all DBCC CHECKDB command integrity check options:

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
DBCC CHECKDB
    ( 'database_name'
            [ , NOINDEX
                | { REPAIR_ALLOW_DATA_LOSS
                    | REPAIR_FAST
                    | REPAIR_REBUILD
                    } ]
    )    [ WITH { [ ALL_ERRORMSGS ]
                    [ , [ NO_INFOMSGS ] ]
                    [ , [ TABLOCK ] ]
                    [ , [ ESTIMATEONLY ] ]
                    [ , [ PHYSICAL_ONLY ] ]
                    }
        ]
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

 

'database_name'
Replace this argument with your database name for which you want to check object allocation and structural integrity. If you have not specified it, it takes current database as a default value.
 

NOINDEX
This argument used to specify that non clustered indexes for non system tables should not be checked. It decreases the overall execution time because it does not check non clustered indexes for user-defined tables.

For using following three repair options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST,  REPAIR_REBUILD, the given database_name must be in single-user mode.

REPAIR_ALLOW_DATA_LOSS:
This argument used to correct allocation errors, structural row or page errors, and deletion of corrupted text objects. This repair option can result some data loss. The repair may be done under user transactions which allow the user to roll back all the changes made. If this repair are rolled back & the database still contain errors, should be restored from a backup. It can Perform all the repairs actions that done by REPAIR_REBUILD

REPAIR_FAST:
This option can be done quickly & have no risk of data loss. It used to perform minor repair actions such as repairing extra keys in non clustered indexes.

REPAIR_REBUILD:
It can Performs all the repairs actions that done by REPAIR_FAST &  can be done without risk of data loss.

All arguments that are mentioned after “With” in the syntax used to display the error messages.

ALL_ERRORMSGS
It shows an unlimited number of errors per object.

NO_INFOMSGS
It is used display the current database File Name, Database Name, Total Extend, Used Extent, Field ID and File Group.

TABLOCK
It is a cause to DBCC CHECKDB run faster on a database under heavy load, but it decreased the concurrency available on the database while DBCC CHECKDB is running.

ESTIMATE ONLY
It shows the estimated amount of tempdb space needed to run DBCC CHECKDB with all of the other specified options.

PHYSICAL_ONLY
It is designed to check physical consistency of the database. It also detects disk errors, controller issues or other hardware-based problems.

Conclusion: You should regularly run consistency checks. If DBCC CHECKDB command indicates any Errors message in the database, it should be corrected immediately.

»»  Read More...