Database maintenance is an important aspect of database management. As a DBA, you need to check for any hardware malfunction and network connectivity anomalies. Aside from physical maintenance, you are also tasked to check database integrity and patches. Sounds a lot, huh?
Well, those are just the basics. But, for SQL Server DBAs, we have dbcc checkdb as a reliable tool for checking database integrity. It checks the physical and logical integrity of your SQL Server database instances.
But, what if you have an alternative to running dbcc checkdb manually? Let’s explore an alternative solution.
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 corruption. dbcc checkdb 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 shown below). You need to specify which database you want to do an integrity check on.
Another option is to use Microsoft SQL Server’s database automation tools to do maintenance plans, as shown in the following image.
Open Maintenance Plan and in the tool section, click dbcc checkdb.
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.
When executed in Microsoft SQL Server, it needs a comprehensive reading and analysis from the DBA to understand the contents. In addition, if you want a more systemic way of recording historic data, you can create stored procedures and integrate dbcc checkdb in your database. Now, I won’t be showing the entire process since it will take significant programming knowledge to develop your own script. In the meantime, dbcc checkdb is a handy tool.
What dbcc checkdb Job?
In dbWatch Control Center, dbcc checkdb is found among its Maintenance Jobs. When installing a database instance, make sure that the dbcc checkdb task is ticked so that it is installed as well. You can also add the job to the instance by manually configuring the Jobs by right-clicking on the instance and selecting "Configure Jobs".
dbWatch dbcc checkdb job is a pre-configured and automated version of dbcc checkdb for Microsoft SQL Server Management Studio. This job will let you automate and simplify dbcc checkdb runs saving you time and effort.
Once the dbcc checkdb is installed in your Microsoft SQL Server, you can configure the parameters or its schedule. We will go first 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 some help, click the "?" and it will 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 at regular intervals 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.
Like the example above, I set the dbcc checkdb to run at 6:15 AM from Mondays through Fridays. You can also specify when you want the Job to execute.
For instance, you can set it to run at 9:25 PM every Wednesday and Saturday for every two weeks.
You can also set the execution time in intervals like every 23 hours. Setting it once or twice per week can be sustainable as larger databases will take more time to check.
Once you are set, wait for dbWatch Control Center to execute the maintenance job. You can go to the monitoring module and open the instance as a means of checking. Just click the maintenance job for dbcc checkdb, and you will find all the related jobs from some other cases.
After execution of the Job, you can see the details of by clicking Job and selecting Details.
You can see the run details in a separate window.
But this is just the instance view. dbWatch Control Center truly shines in database farm management, meaning - if you have lots of instances, it will truly be an indispensable tool. In this example, we have 17 different instances currently being monitored by dbWatch Control Center.
Six of them are SQL Server Instances. What’s great about dbWatch Control Center’s Farm View is the ability to see the status of all dbcc checkdb Jobs and their results. For good measure, it’s not just limited to one instance but spread across multiple SQL Server instances.
The magic of maintenance checks does not end there. Other Maintenance Jobs are also available for execution. Just remember to install all the other Maintenance Jobs for it to be shown in the other maintenance tabs.
So, how is this a better alternative than running dbcc checkdb manually?
Remember, Microsoft SQL Server Management Studio is only connected to one instance at a time. You can only perform dbcc checkdb in that instance. Compare that with dbWatch Control Center where you control multiple instances under one screen while executing dbcc checkdb simultaneously. YES, simultaneously! You don’t have to wait for one instance to finish before you can execute another.
Think of it as executing multiple Jobs in parallel and each Job is independent of each other. This saves you time and effort in conducting your DBA workflow. Add the bonus of fixing your database with one click, then you are making the tool work for you so you can focus more on other DBA-related tasks.
Summary
Running dbcc checkdb is easy to do with dbWatch. What dbWatch offers is automating AND SIMPLIFYING the execution of dbcc checkdb on all your instances. This will ensure you always do consistency checks with no time needed from you. 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 #DBA #DatabaseReporting #DatabaseMonitoringSolution #SQLServer #MicrosoftSQLServer
-----------------
Other Blogs:
Running Databases - Docker vs. Alternatives
Monitor your SQL Server Indexes
