Walkthrough example for applying FixSQL against SQL Server instance

Applies to

ApexSQL Manage


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.

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:

Run health check window in ApexSQL Manage

From the Rules tab, check the Databases box to include all database-related health check rules:

Rules tab in the Run health check window within ApexSQL Manage

Under the Summary tab, the list of chosen parameters can be reviewed:

Summary tab of the Run health check window

Click the OK button to perform the analysis:

OK button in the ApexSQL Manage

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:

Result summary in the SQL manage instance tool

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

Health check grid in the SQL manage instance tool

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:

Database Full Recovery mode must be enabled rule

Once the mentioned rule is selected, in the Result tab below the grid, information regarding its name, object type, result, etc. is shown:

Result tab of the Database Full Recovery mode must be enabled rule

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:

Violation tab of the Database Full Recovery mode must be enabled rule

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:

FIxSQL tab of the Database Full Recovery mode must be enabled rule

Commit Fix SQL script

To commit the mentioned SQL script, after ensuring the desired rule is checked from the grid, click the FixSQL button:

Fix SQL Button within the SQL manage instance tool

From the Fix SQL editor, a script for the previously checked rule is shown:

Fix SQL editor within the SQL manage instance tool

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

Fix SQL editor options within the SQL manage instance tool

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:

Messages box of the Fix SQL editor

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 database recovery mode


After database recovery mode