Troubleshooting

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

I want to update to the latest version.

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

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.) You can also download the latest version manually.

---

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:

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:

---

I want to edit a connection’s settings.

To list the servers you’ve configured, run:

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:

Then add it back in again following the instructions in the Quick Start Guide. 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 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:

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

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:

---

I get an error “The client and server cannot communicate, because they do not possess a common algorithm.”

You’ve disabled TLS 1.1 on your SQL Server. Right now, that’s not technically supported, but you may be able to get SQL ConstantCare to work by adding the following line of code to the constantcare.exe.config file under <Runtime>:

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:

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):

Then add it back in again following the instructions in the Quick Start Guide. 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:

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:

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:

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. Then, email us at Help@BrentOzar.com telling us the old & new email addresses so we can connect your history on the back end. If you don’t email us, the new email address wouldn’t have any performance baselines.

---

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 sqlconstantcare@brentozar.com.

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:

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

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 Guide.

---

Help! Something else is broken!

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