This article provides an insight into the basic options of Fix SQL editor and describes the process of executing SQL scripts to remedy failed health check rules against a SQL Server instance.
It is not an uncommon situation to have the means to investigate something that helps to successfully locate the issue and then not having the right tools to fix it.
Translated into today’s topic, it’s great to have an option to analyze a SQL Server instance in search for possible issues, but what if there is a possibility to go the extra mile and fix those issues.
ApexSQL Manage is the SQL manage instance tool that provides the necessary resources to deeply investigate SQL instance issues and then provide specific SQL scripts that will instantly address those issues and successfully fix them.
For the purpose of this guide, let us perform a set of SQL database health check rules against the desired SQL instance.
To accomplish this feat, head over to the Health tab and click the Run button. In the Servers tab of the Run health check window, check the box in front of the targeted SQL Server instance:
From the Rules tab, check the Databases box to include all database-related health check rules:
Under the Summary tab, the list of chosen parameters can be reviewed:
Click the OK button to perform the analysis:
Once a SQL instance high-level analysis is performed, its results, along with the statistics, are shown in the Result summary window. Click the Close button to exit the window:
The performed set of SQL Server instance rules with its details is displayed in the grid and spread across the appropriate columns:
Let’s focus here on the Result grid column, preferably on the rules with “Fail” result. For instance, let’s check here the Database Full Recovery mode must be enabled rule. This rule recommends that any database on a SQL Server instance should be in the Full recovery mode:
Once the mentioned rule is selected, in the Result tab below the grid, information regarding its name, object type, result, etc. is shown:
Under the Violation tab, besides its severity level, the Advice is given in the form of a best practice. This rule recommends that the best practice is to always set the database to full recovery mode:
Under the Fix SQL tab, there is a SQL script that will, once launched, convert all databases on targeted SQL Server instance to full recovery model:
To commit the mentioned SQL script, after ensuring the desired rule is checked from the grid, click the FixSQL button:
From the Fix SQL editor, a script for the previously checked rule is shown:
Following is a brief description of the Fix SQL editor options:
To launch the script, click the Execute button. Once the script is completed, the Messages box will be shown stating the script was completed successfully or failed with errors:
It is possible to directly verify the FixSQL script effect by doing the before-after comparison of recovery mode for an arbitrary database that belongs to SQL Server instance the previous script was performed against.
Before:
After:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center