Summary: You need to check database integrity to ensure that the database is fine. In this blog, you’ll learn how often you can check the database integrity. You’ll know how to run and schedule a database integrity check. You’ll also find about a professional SQL repair tool that can repair the database, if the integrity check fails.
In SQL Server, the DBCC CHECKDB command verifies the logical and physical integrity of the database. It verifies the integrity of tables, catalogs, disk space allocation, content of indexed views, links between table metadata and file systems, and more.
How often should you run a database integrity check?
It is a common practice to do it once a week. However, it depends on the backups. You may want to check the database integrity before doing a database backup. If you back up every day, you may need to check database integrity daily before the backup.
How to run the database integrity check?
The DBCC CHECKDB command verifies the database integrity. There are some arguments that you can include with the command, such as:
- NOINDEX: It excludes the nonclustered index check.
- NO_INFORMSGS: It excludes informational messages.
- TABLOCK: It locks the tables to get faster results.
- ESTIMALTEONLY: It estimates the tempdb space required to run the command.
- PHYSICAL_ONLY: It checks only the physical structure of the database.
How to schedule or automate database integrity check?
The SQL Server Agent is used to schedule and automate tasks. With the SQL Server Agent, you can run T-SQL scripts, including the DBCC CHECKDB command. You can also run the command-line or MDX, XMLA, and DAX scripts.
- To create a job, in the SQL Server Agent, go to the Jobs node and select New Job.
- Write a name for the job and go to the Steps page.
- On the step page, press the New button.
- In the New Job window, write a step name. In the Type, make sure that the Transact-SQL script is selected.
- In the Command section, you can use the DBCC command. Then, press OK.
- Go to the Schedules page and press the New button.
- Write a new schedule name. You can run the job weekly, daily, or hourly. You can also specify the start time.
Is it possible to run maintenance plan wizard to schedule the integrity check?
Yes, SQL Server includes an option to easily create a maintenance plan in the database. To use the Maintenance Plan Wizard, open SSMS and go to the object explorer. Expand the Management option, right-click the Maintenance Plans, and select the Maintenance Plan Wizard.
- In the Maintenance Plan Wizard window, click Next.
- Write a name for the maintenance plan. Select an account to run the job. By default, the SQL Server Agent service account is used. Press the Change button to change the schedule.
- Select weekly, daily, or hourly by using the Occurs box, configure how often do you want to run the maintenance check, and press OK.
- Press Next. In Select Maintenance Tasks, select the Check Database Integrity option and press Next.
- In the Select Maintenance Task Order window, press Next.
- Select the databases to maintain and press Next.
- You can choose a path for the report.
- Finally, press the Finish button.
- In the final window if everything is green, close the wizard.
- In SQL Server Agent, in the Jobs node, you will see the new job created.
What to do if the database integrity fails?
The DBCC CHECKDB command may fail. If that happens, you can try to use the command with the REPAIR_REBUILD option.
If that option fails, you can try to repair the database with the REPAIR _ALLOW_DATA_LOSS or using a third-party software, such as Stellar Repair for MS SQL. This software can easily repair the database (mdf) file without any data loss. You may need to bring the database offline to repair it.
Conclusion
In this article, we have learned how often we can run the database integrity check. We also discussed how to run and schedule a database integrity check. If the integrity check fails, you need to repair the database. To avoid data loss, you can use a third-party SQL repair tool, such as Stellar Repair for MS SQL.
