Troubleshooting

Got a question that the Quick Start didn't answer, or something went wrong? When the SQL ConstantCare® data collector doesn't collect data, here are common symptoms to look for.

  1. If you get a connection failure when connecting to YOUR database server, and it happens instantly, that's a username/password problem.
  2. If you get a connection failure when connecting to YOUR database server, and it takes 15-30 seconds, that's a servername/port/firewall problem.
  3. If you get a connection failure or fatal error connecting to OUR servers (like WebExceptions or S3), that's either an outdated version of SQL ConstantCare, or it's a stateful packet inspection firewall in your environment that's blocking connections to us.

That 1-2-3 list solves every single issue we've ever had. I know, you think your situation is different, ha ha ho ho, but slow down and read through that list again. Then, here are more details for solving the above issues:

When troubleshooting, start by updating to the latest version.

Open a command prompt as administrator (that part is really important) and type: 

cd %localappdata%\ConstantCare\current
ConstantCare

That will trigger an automatic update.

The application updates itself automatically as long as it can connect to the Internet (which it has to do anyway if you're sending us results.)

What ports do I need to open?

On whatever machine you install the ConstantCare.exe client (typically a jump box or utility server), the exe will connect to two things:

  • Connecting to your SQL Server - so you'll need to open port 1433 or whatever port you're using to connect. (Most people don't need to know this part, but just in case: we use TLS1.0/1.1.)
  • Connecting to our server to upload the data - that's constantcare-prod-intake.s3.amazonaws.com, constantcareapi.brentozar.com, kms.us-east-1.amazonaws.com, and s3.amazonaws.com on ports 80 and 443. These are usually open by default in most shops - they're the same ports you use for surfing the web and reading this very page.

I want to know if data collection is working.

Try running it manually - open a command prompt and type: 

cd %localappdata%\ConstantCare\current
ConstantCare.exe --skipupdate

ConstantCare.exe will then try to connect to each of your servers to poll data. If you see any errors - especially "SQL Connectivity Failed" - that means we can't connect to your server.

If you want to test a single server by itself, specify its connection name: 

ConstantCare.exe --connectionname MyConnectionName --skipupdate

I want to edit a connection's settings.

To list the servers you've configured, run: 

ConstantCare.exe --listconnections

Look at the connection names carefully - you may have given a server a name that's different from its network name, like maybe you called one SharepointProduction. Using the connection name, you can remove it: 

ConstantCare --removeconnection --connectionname MyConnectionName

Then add it back in again following the instructions in the Quick Start. Note that if you try editing the connection (rather than deleting & re-adding), you always have to specify the time zone.

The upload fails with a fatal message.

It either means that you've disabled TLS 1.0/1.1, disabled the SHA hash, or there's a a firewall between you and Amazon that's blocking traffic on ports 80 or 443. You need to have s3.amazonaws.com open on ports 80 and 443.

If your IT team comes back and says, "no no, it's cool, everything's open for that," and you continue to get a fatal message about uploading, it's most likely a network appliance doing stateful packet inspection and trying to block outbound encrypted zip files.

When I run ConstantCare.exe manually, I get a permissions error or "Fatal - Message"

That means the app was updated, but run from the command prompt without being administrator. (Man, I hate Windows permissions.) To fix it, you'll need to open a new command prompt as administrator (seriously important) and type: 

cd %localappdata%\ConstantCare
rmdir current
dir

Then look for the highest-version app directory, like app-0.26.0, and type: 

mklink /d current app-0.26.0

Replacing app-0.26.0 with whatever the highest version is on your system. That will remove the "current" directory and recreate the symbolic link to the current version of the application.

After you've fixed it, run a collection manually, and then email us to make sure we got the data: 

schtasks /Run /TN "ConstantCare"

The connection to my SQL Server is timing out, or I want to change a connection's name, login, or password.

I'll assume you've already tackled the basics (you can connect with SSMS, there's not a firewall issue, the server isn't down, etc.)

Start by listing the connections to see the name of the one that's timing out: 

ConstantCare --listconnections

Then just to be safe, remove it - this doesn't affect your data history - and replace MyConnectionName with whatever yours is that's timing out. Remember, it may not be the server name (depending on how you set it up initially): 

ConstantCare --removeconnection --connectionname MyConnectionName

Then add it back in again following the instructions in the Quick Start. Note that if you try editing the connection (rather than deleting & re-adding), you always have to specify the time zone.

I want advice on my queries and execution plans.

Sure, to send us those, include the --sendplancache true parameter when adding or editing a connection, like this: 

ConstantCare.exe --editconnection --connectionname MyFriendlyServerName --sendplancache true --datasource MyServerName --timezone America/New_York

Be aware that your queries could contain confidential information.

If you've got a lot of servers, it may be easier to edit your UserSettings.config file manually. In a text editor, open %localappdata%\ConstantCare\Current\UserSettings.config, and look for the sendplancache lines: 

<?xml version="1.0" encoding="utf-8"?>
<userSettings>
  <add key="SQL2017B_tz" value="America/Los_Angeles" />
  <add key="SQL2017B_sendplancache" value="false" />
</userSettings>

Just change the sendplancache values from false to true.

I want to troubleshoot a single server's connection.

To test just a single connection, run: 

ConstantCare.exe --skipupdate --connectionname MyConnectionName

And replace that last thing with your connection name, like SQL2017PROD1. The SkipUpdate switch avoids automatic updating of ConstantCare.exe just because when you're testing, you wanna focus on only seeing messages for the server you're trying to troubleshoot.

I want to change the email address for the emails.

Go to the machine where ConstantCare.exe is installed, and edit the email address in this file:

%localappdata%\ConstantCare\current\usersettings.config

Save it, and on your next data collection, the emails will go to the new address. However, keep in mind that this is considered a new account - we don't transfer data between emails due to security reasons. Any data trending will start over from scratch in the new account.

I'm not receiving emails.

Check your spam & junk mail folders. If you're using a distribution list, make sure that it accepts emails from [email protected].

We send you two kinds of emails:

  • Automated daily emails - ideally, within a few minutes of receiving your data, we send a list of automated recommendations to improve health and performance. However, if we don't find anything urgent for you to do on a server, we don't send an email for it.
  • Manual weekly emails - about once a week, depending on our client & training schedules, we analyze your data and send a personal email (from the same address).

If you haven't received any of those in a couple of days, feel free to shoot us an email to make sure we're getting your data.

I want to mute a recommendation.

Sure! Just hit reply on the email with the recommendation and tell us why you want to mute it. Depending on the recommendation, we may have a lively discussion about it, heh. As your mentors, for example, we want to make sure that you back up every database.

I want to collect and send in data right now.

Maybe you missed your regularly scheduled task time, or your server was down, or you're just getting antsy. You can run the scheduled task whenever you want: 

schtasks /Run /TN "ConstantCare"

Or run the collector for a specific server by going to a command prompt, navigating to the ConstantCare folder, and running: 

ConstantCare.exe --datasource MyServerName --userid MyUserName --password MyPassword --skipupdate

If you use Windows authentication, then you can leave out the userid and password.

There's no extra charge for sending in data more frequently, or for more servers.

I want to collect data from a server in the cloud or in a colo.

If you don't have always-on connectivity to a SQL Server, like if you need to VPN in to connect to it, then you can just collect data now and then manually if you like. Make the VPN connection, and then follow the instructions above about collecting and sending in data right now.

I got a "SQL ConstantCare File Not Loaded" error email.

That means when we went to process one of your uploaded files, some of the required data was missing. Common causes for this include:

  • SQL Server restart during collection - like one of ConstantCare.exe's queries timed out midway through because your cluster/mirror/AG decided to fail over at that exact moment.
  • Insufficient permissions - if you set up a SQL account for data collection, and you took away too many permissions, we didn't get the minimum required. We need SA-level permissions for everything, but if you want to try less, you can use VIEW SERVER STATE as a minimum. We won't get things like checkdb or backup success, but you'll still get a lot of useful diagnostic information.
  • Broken installation - if you tried editing ConstantCare.exe's files manually, and you played around in some of the query files, you might have gone and done something not-so-bright. No worries, we've all been there. Remove the ConstantCare folder, and follow the instructions again from the Quick Start.

Help! Something else is broken!

No problem - contact us and include as much details as you can, like error messages and screenshots.

Complete and Continue