SQL Server Checkup

Run this application from your desktop, and it will:

  • Connect to the SQL Server of your choice
  • Run a series of low-priority diagnostic queries
  • Put that data into a spreadsheet and a series of files
  • Zip them together into one file

How to Use the SQL Server Checkup

Open a command prompt in the folder where you unzipped it, and run:

Windows authentication:

SQLServerCheckup.exe --datasource MyServerName

SQL Server authentication:

SQLServerCheckup.exe --datasource MyServerName --userid MyUserName --password MyPassword

Azure SQL DB:

SQLServerCheckup.exe --datasource MyServerName --userdb MyDatabaseName --userid MyUserName --password MyPassword

If you use tricky characters in your server name, user name, or password, like quotation marks or spaces, you’ll need to surround it with double quotes. For example, if my password is @#!, I might want to use “@#!”.

You don’t have to run this on the SQL Server itself – you can run it from any desktop or laptop with at least .NET 4.5.2 (circa 2014.)

When it finishes, go into the Output folder, grab the zip file, and send that to us. We will start reading the diagnostic data and making our assessments.

Requirements

  • SQL Server 2008 and newer are supported – if Microsoft doesn’t support it, neither do we.
  • .NET 4.5.2 or higher – which came out way back in 2014.
  • Sysadmin permissions – because to query for things like CHECKDB success, you have to be SA. The app will run with lower permissions like VIEW SERVER STATE, and it’ll throw warnings when it’s unable to query those SA-only functions, but you’ll still get a ton of usable diagnostic data.
  • Cloud compatibility:
    • Amazon RDS SQL Server – fully supported
    • Azure Managed Instances – fully supported
    • Azure SQL DB and Hyperscale – supported as long as you pass in a database name in the command line as shown above. One of the queries (a server health check) will not run, and will throw a yellow error – that’s because Azure SQL DB doesn’t support master database diagnostics yet.
  • Not supported: other database platforms (MySQL, Postgres, Oracle, CosmosDB, Azure Data Warehouse, Parallel Data Warehouse, etc.)

What Queries It Runs

To see the queries, go into the \Resources\SQLServerCheckup folder. They are a collection of industry-standard open source scripts that check for things like missing backups, corrupt databases, misconfigured memory, and more.

They are very lightweight: they don’t start traces with Profiler or Extended Events. They’re designed to capture diagnostic data quickly with a minimum of overhead.

Nervous about what data is getting gathered? No problem: run it against a test or development server first. After it finishes, look in the Output folder and you can see the output.

To learn more about the queries, read the Queries page.

Troubleshooting Common Issues

If you get a connection failure after 30 seconds, odds are there is a firewall between your desktop and the SQL Server. Could be Windows Firewall, could be something else.

Are you out of the domain? If your SQL Server instance is using Windows Authorization and you are not on a machine in the proper domain you need to switch the Command Prompt to run in that user context. To do this first run:

runas /noprofile /netonly /user:MyDomain\MyUserName cmd

When the new window pops up follow the original connection instructions from there.

If you get a connection failure instantly, that means we were able to connect to the SQL Server, but your login credentials weren’t accepted. If you’re using SQL authentication with a password that contains symbols, try enclosing the whole thing in double quotes.

If it runs, but shows yellow errors, that’s okay – we just had problems collecting some kinds of data. For example, you may have databases in an older-than-supported compatibility level. We can still likely get enough data to analyze and make recommendations.

If your version has expired, no problem. Our newsletter subscribers can always download the most updated version. Just re-download from the SQL iSpy™ Download page as needed.

For more common issues, see the Troubleshooting page.