Applies to
Summary
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.
Introduction
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.
Perform high-level analysis
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:
Where to find results of health check analysis?
The performed set of SQL Server instance rules with its details is displayed in the grid and spread across the appropriate columns:
- Name – name of a rule
- Description – brief description of a rule
- Server – name of the server rule is analyzing
- Result – a health check rule result: Pass or Fail
- Severity – a health check rule severity: High, Medium or Low
- Category – the category rule belongs to
- Date checked – date and time when a rule was launched
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:
Commit Fix SQL script
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:
- Save – saves a script into the desired location for later use
- Execute – launch fix SQL script immediately against selected SQL Server instance
- Check syntax – check script syntax for errors
- Connect – connects to desired SQL instance to run the script against
- Undo – undoes the previous action
- Redo – redoes the previous action
- Cut – cuts selected part of the script
- Copy – copies selected part of the script
- Paste – paste selected part of the script
- Messages – shows the Messages box at the bottom
- Find – finds searched words
- Replace – replace searched words
- Word wrap – turns on/off word wrap option
- Line numbers – adds/removes numbers in front of the lines
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:
Before-after comparison
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:
