CHECKDB

CHECKDB is a command used to check the physical and logical integrity of all the objects in a SQL Server database. It's a crucial maintenance task to ensure the health and reliability of your database. Here are the key points about CHECKDB:

1. Database Integrity Check: CHECKDB is used to perform a thorough integrity check on the specified database. It examines the physical and logical consistency of the database's tables, indexes, and other objects.

2. Detects Corruption: One of the primary purposes of CHECKDB is to detect and report any physical corruption or inconsistencies within the database. This includes problems like data page corruption, torn pages, and other forms of data corruption that could affect the reliability of your data.

3. Repair Option: Depending on the severity of the corruption, CHECKDB offers a repair option (REPAIR_ALLOW_DATA_LOSS) that can attempt to fix some types of corruption. However, this option should be used with caution as it might result in data loss.

4. Three Types of CHECKDB:

   • DBCC CHECKDB: Checks the entire database for corruption and logical integrity issues.

   • DBCC CHECKTABLE: Checks a specific table and its nonclustered indexes for corruption.

   • DBCC CHECKFILEGROUP: Checks the objects within a specified filegroup for corruption.

5. Consistency Checks: CHECKDB performs various consistency checks on the database, including checks for referential integrity, index structure, and allocation consistency.

6. Execution Frequency: It's recommended to run CHECKDB on a regular basis to ensure the ongoing health of your database. The frequency depends on factors like the criticality of the database and the rate of data modifications.

7. Performance Impact: Running CHECKDB can have a performance impact on the database as it involves scanning and validating data pages. For large databases, it's advisable to schedule the check during off-peak hours to minimize disruptions.

8. Command Syntax:

DBCC CHECKDB [ ( database_name | database_id | 0 ) [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ]

Output: After running CHECKDB, you'll receive a report indicating the results of the checks. This report will provide information about any corruption or inconsistencies found within the database.

DBCC CHECKDB Syntax and Options

• DBCC CHECKDB ('DatabaseName')

• NOINDEX - Specifies that intensive checks of non-clustered indexes for user tables should not be performed.

• NO_INFOMSGS - Suppresses all information messages.

• PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database.

• TABLOCK - Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

• DATA_PURITY - Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns

DBCC error messages examples

• The In-row data USED page count for object "tablename", index ID 2, partition ID 608313809829888, alloc unit ID 608313809829888 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) The In-row data RSVD page count for object "tablename", index ID 2, par... The step failed.

• DBCC UPDATEUSAGE will correct the page and row count inaccuracies in the catalog views.

• Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details. Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. CHECKDB found 0 allocation errors and 2 consistency errors in table 'tablename' (object ID 2088535921). CHECKDB found 0 allocation errors and 2 consistency errors in database 'tablename'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Database).

• The second error reports data corruption. The error mentions using repair_allow_data_loss as the minimum repair level. This means you can run the statement with this argument, but you may lose data.

• restoring to a backup if you can. You need to make sure the backup doesn't contain corrupted data and you want to make sure there is no data loss.

• REPAIR_REBUILD - Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

• REPAIR_ALLOW_DATA_LOSS - Tries to repair all reported errors. These repairs can cause some data loss.

DBCC CHECKDB Examples

• DBCC CHECKDB(N'AdventureWorks2016') WITH NO_INFOMSGS

• DBCC CHECKDB(N'AdventureWorks2016‘,REPAIR_REBUILD

• DBCC CHECKDB(N'AdventureWorks2016',REPAIR_ALLOW_DATA_LOSS)

• Single User Mode

• ALTER DATABASE AdventureWorks2016 SET SINGLE_USER

• Multi User Mode

• ALTER DATABASE AdventureWorks2016 SET MULTI_USER

What is page level restore/recovery

Page-level restore or recovery is a database management technique that allows you to recover individual damaged or corrupt database pages without having to restore the entire database from a backup. This is particularly useful when you have a large database and only a small portion of it is affected by corruption or other issues. Page-level restore enables you to target specific problematic pages and replace them with their corresponding pages from a backup, minimizing downtime and data loss. Here's a breakdown of page-level restore:

1. Granularity: Unlike a full database restore, which involves restoring the entire database from a backup, page-level restore operates at a much finer granularity. It allows you to restore specific data pages within a database that have been identified as corrupt or damaged.

2. Corruption Scenarios: Page-level restore is commonly used when certain database pages become corrupt due to hardware failures, software bugs, or other unforeseen issues. Instead of restoring the entire database, you can isolate the affected pages and restore them.

3. Reduced Downtime: Page-level restore can significantly reduce downtime because you only need to restore and recover the specific pages that are problematic. This is especially beneficial for large databases where a full restore could take a considerable amount of time.

4. Point-in-Time Recovery: Some database systems allow you to perform point-in-time page-level recovery, which means you can restore a specific page to a previous point in time, preserving data consistency and integrity.

5. Backup Requirements: To perform page-level restore, you need a recent backup that contains the required page data. Regular backups are essential to ensure that you have the necessary recovery options in case of corruption.

6. Transaction Logs: In addition to the backup, transaction logs play a crucial role in page-level recovery. Transaction logs contain a record of changes made to the database since the last backup. These logs are used to bring the restored page up to the current point in time.

7. Steps for Page-Level Restore:

   • Identify the corrupted or damaged page within the database.

   • Restore the database from the most recent backup that includes the required page.

   • Apply transaction log backups or logs to bring the database to the desired point in time.

   • Replace the corrupt page with the restored page from the backup.

8. Limitations and Considerations:

   • Page-level restore might not be feasible in all database systems or configurations.

   • It requires a good understanding of the database structure and recovery process.

Depending on the extent of corruption, there might be cases where a full database restore is still necessary.

Restoring Master database

Restoring the master database in SQL Server is a critical operation that should be approached with caution and careful planning, as the master database contains essential system-level information about the SQL Server instance. The master database stores information about database objects, logins, linked servers, and other configuration settings. If the master database becomes corrupt or is lost, it can have a significant impact on the SQL Server instance's functionality.

Here's a general outline of the steps to restore the master database:

1. Backup Master Database: Before you can restore the master database, you need a valid backup of the master database. This backup should include all necessary data and transaction log information.

2. Prepare for Restore:

   • Ensure you have a backup of the master database.

   • Stop the SQL Server instance to prevent any ongoing changes to the master database.

   • Ensure that you have the necessary permissions to perform the restore operation.

3. Restore the Master Database:

   • Start the SQL Server instance in single-user mode to prevent other connections.

   • Open a command prompt or SQL Server Management Studio (SSMS) query window with administrative privileges.

   • Run the restore command using the backup file you prepared:

       sqlCopy codeRESTORE DATABASE master
       FROM DISK = 'C:\Path\To\Backup\master_backup.bak'
       WITH REPLACE;
     

   • The WITH REPLACE option is necessary to replace the existing master database.

4. Restart the SQL Server Instance: After the restore is complete, restart the SQL Server instance normally.

5. Verify and Test:

   • Check that the master database has been restored successfully and that the SQL Server instance is functioning as expected.

   • Test various system and user databases to ensure they are accessible and operational.

6. Reconfigure Server Settings: If your master database contained specific server-level configurations (such as logins, linked servers, etc.), you might need to reconfigure these settings after the restore.

It's important to note the following considerations:

1. Backup and Recovery Strategy: Having a comprehensive backup and recovery strategy in place is crucial to avoid loss of data and minimize downtime in case of a disaster.

2. System Databases: Along with the master database, other system databases like model and msdb might also need to be restored in certain scenarios.

3. Documentation: Make sure you have clear documentation of your SQL Server instance's configuration, so you can quickly reconfigure settings after a restore.

4. Practice: If you're not familiar with restoring the master database, it's recommended to practice the process in a controlled environment before attempting it on a production server.

Consultation: If you're uncertain about any step in the process, it's advisable to consult with experienced database administrators or follow Microsoft's official documentation.

steps for Exercises

1. Backup the database BACKUP DATABASE [master] TO DISK = N'C:\SQL_BACKUPS\master_backup_test.bak' WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

   GO

2. Run those commands in CM as admin before restoring

   net stop mssqlserver

   net start mssqlserver /m

3. Restore Database restore database master from disk = 'C:\SQL_BACKUPS\master_backup.bak' with replace;

A maintenance plan

A maintenance plan in the context of a relational database management system (RDBMS) like Microsoft SQL Server is a set of scheduled tasks and activities designed to ensure the health, integrity, and optimal performance of the database environment. A well-structured maintenance plan helps manage routine tasks such as backups, index optimization, database integrity checks, and other essential database-related activities. Here's an overview of what a maintenance plan entails:

1. Backup Strategy:

   • Regular Database Backups: Scheduled backups of databases to ensure data recoverability in case of data loss or hardware failure.

   • Full, Differential, and Transaction Log Backups: Depending on the recovery model of the database, different types of backups might be scheduled to achieve the desired balance between recovery options and backup size.

2. Database Integrity Checks:

   • Regular Checks: Scheduled consistency checks to identify and repair any corruption or logical inconsistencies in the database.

   • Tools like DBCC CHECKDB can be used for this purpose.

3. Index Maintenance:

   • Index Reorganization or Rebuild: Scheduled maintenance tasks to optimize the performance of indexes, which can become fragmented over time due to data modifications.

   • Properly maintained indexes enhance query performance.

4. Statistics Update:

   • Update Statistics: Regularly updating query optimization statistics to help the SQL Server query optimizer make accurate decisions about query execution plans.

5. Database Cleanup:

   • Data Archiving: Move or remove outdated or unnecessary data from the database to improve performance and manage storage.

   • Transaction Log Management: Regularly shrinking or managing transaction log files to prevent them from consuming excessive disk space.

6. Server and Job Monitoring:

   • Monitoring and Alerting: Setting up alerts and notifications to receive alerts about specific conditions, such as failed backups or excessive database growth.

   • Job History Review: Monitoring the execution history of maintenance tasks to identify any issues or patterns.

7. Documentation:

   • Maintain Documentation: Keep records of your maintenance plan, including schedules, configurations, and any changes made over time.

   • Documentation is crucial for ensuring consistency and troubleshooting.

8. Scheduling and Automation:

   • Define Maintenance Plan Schedule: Schedule maintenance tasks to run during off-peak hours to minimize impact on database performance.

   • Use SQL Server Agent: Utilize SQL Server Agent to automate and schedule maintenance tasks using Transact-SQL scripts, SQL Server Integration Services (SSIS) packages, or third-party tools.

9. Testing and Adjustments:

   • Regular Review: Periodically review the maintenance plan to ensure its effectiveness and relevance.

   • Adjustments: Adjust the plan as database usage patterns change or when you identify areas for improvement.

10. Security Considerations:

    • Limit Access: Ensure that only authorized personnel can modify or execute maintenance plans.

    • Protect Sensitive Data: When dealing with backups and other data, follow security best practices to prevent unauthorized access.

Examples for weekly/daily/Hourly

weekly

Daily Diffreincal backup and hourly transaction log backup