<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

Optimizing database integrity – a better alternative for dbcc checkdb

Posted by Rey Lawrence Torrecampo on Mar 25, 2022 9:00:00 AM

Checking for database corruption is a task DBAs need to do. Maintaining a database is a routine that should not be taken lightly, as database operations are the bread and butter of a successful business. In this blog, we will look at alternatives for dbcc check db. 

1-Jan-13-2022-04-54-29-74-AM

 

 

What is DBCC CHECKDB? 

DBCC CHECKDB is a database console command for SQL Server Database that checks the physical and logical integrity of objects of a database instance. This method detects potential SQL Server Database corruptions. DBCC CHECK DC can be used in four categories: Maintenance, Miscellaneous, Informational, and Validations. 

You can run DBCC CHECKDB in SQL Server Database using a query command as seen below. You need to specify which database you want to do an integrity check on. 

Graphical user interface, application

Description automatically generated

 

Or you can use Microsoft SQL Server’s database automation tools to do maintenance plans, as seen in the following image. Open Maintenance Plan and in the tool section, click on DBCC CHECKDB from the options. 

 

Graphical user interface, text, application, email

Description automatically generated

 

Both methods are available when using Microsoft SQL Server Management Studio. Any DBA, regardless of experience, can automate DBCC CHECKDB since it is already part of the Microsoft SQL Server database automation tools. 

That is great, but DBCC CHECKDB, when executed in Microsoft SQL Server, needs a comprehensive reading and analysis from the DBA to understand the contents. Is there a better way of checking for physical integrity? 

Yes, there is. In a tool such as dbWatch Control Center, DBCC CHECKDB is found in its maintenance jobs. When installing a database instance, make sure that DBCC CHECKDB is ticked. You can also add the instance by manually configuring the jobs by right-clicking on the instance and selecting “Configure Jobs.” 

Graphical user interface, text, application

Description automatically generated

 

DBCC CHECKDB Job? 

Once the DBCC CHECKDB is installed in your Microsoft SQL Server, you can configure the parameters or its schedule. We will first go over the parameters involved with the Job. The parameters for DBCC CHECKDB and the parameter's description are summarized in the table below.  

 

 

If you are using DBCC CHECKDB without prior knowledge of checking, you can leave the parameters to default. They are already set to check the most crucial user databases in most cases. However, if you want to be more specific about using the database automation tool for DBCC CHECKDB, you can right-click on the job DBCC CHECKDB and select “Configure.” 

 

You can change the values in the configuration window. If you need help, click on the “?” and give you the parameter description. Once you are all set, select “Apply.” 

 

Next, you can modify the scheduled time of execution. Like Microsoft SQL Server’s agent, the Job will be triggered in an interval or at an exact time and date. You can do this by right-clicking on the Job and selecting “Set Schedule.” This will prompt a window to set the desired schedule to run the Job. 

 

Graphical user interface, text, application, email

Description automatically generated

 

Like the example above, I set the DBCC CHECKDB to run at 6:15 AM from Mondays to Fridays. You can also specify when you want the Job to execute.  

Graphical user interface, text, application

Description automatically generated

 

For instance, you can set it to run at 9:25 PM every Wednesday and Saturday for every two weeks.  

 

Graphical user interface, text, application

Description automatically generated

 

Once you are set, wait for dbWatch Control Center to execute the maintenance job. You can quickly go to the monitoring module and open the instance as a means of checking. Just click on the maintenance job for DBCC CHECKDB, and you will find all the related jobs from some other cases. 

Another method is to create a tab and create your own view. This FDL (Farm Data Language) query below shows you all the DBCC CHECKDB, their status and schedule.  

 

 

Graphical user interface

Description automatically generated

 

You can tweak the FDL script to fit what you need or create a view by following the guide in the link

 

Summary 

 

DBCC CHECKDB is easy to implement with dbWatch. What dbWatch offers is the ability to check DBCC CHECKDB among multiple database instances, which is absent when doing it individually. The best way of truly optimizing integrity checks is by automating the process and having an overview across your database environment. 

You can try dbWatch Control Center today. Use this link to download a version of dbWatch Control Center that can monitor up to 5 instances for six months for free:  dbWatch Control Center free license      

If you have any questions or you would like to know more on how dbWatch can assist you in your current enterprise database monitoring and management situation, feel free to contact me directly:  rey@dbwatch.com  or mail presales@dbWatch.com    

For more information, visit www.dbWatch.com or the dbWatch wiki pages     

#DatabaseMonitoring #DatabaseServers #DatabaseInstances  #DBA #DatabaseReporting #DatabaseMonitoringSolution  #SQLServer #MicrosoftSQLServer 

 

-----------------

Other Blogs: 

Running Databases - Docker vs. Alternatives

Monitor your SQL Server Indexes