From time to time I need to monitor the connectivity of a database from a specific users PC. The user might have called and said they are getting randomly disconnected or they are sometimes having problems connecting to a specific application that is using one of my SQL Server databases.  This type of issue can sometimes be a little difficult to track down because the error is not always repeatable.  And if you can repeat the error message, the vendor is not always showing you the full SQL message. Most of the time the vendor has watered down the error message.

To help me troubleshoot this type of problem I have developed two PowerShell scripts. The scripts can run unattended, and the logs can be reviewed at a later time. I like to run the scripts from the user’s PC that is having the problem and from a PC in a different area or on a different subnet. Sometimes I will also run it from the SQL server at the same time. This gives me 3 different log files to compare. These scripts are very lightweight regarding both CPU usage and the load they put on the SQL Server. You do have to be careful about the query that you run. Try to do something like a select count(*) on a small table. 

Basically all you are testing with these scripts are: can you connect to a SQL Server and execute a query. This will run in a loop until you decide to stop it.  If an error appears it will write the error to a log file and try to connect again.

OpenConnQueryCloseConn.ps1 - The first script will open a connection to the SQL Server instance, run a SQL command on a database and then close the connection. This process will continue over and over again until you choose to stop it.

OpenConnLoopQuery_Long.ps1 - The second script will open a connection, leave it open and then execute a query against a database over and over again. The connection is open the entire time until you stop the script.

Both scripts will write to the console and to a log file. If a connection error is encountered by the user, we hope to also see an error message in the log file. When an error is only in one log file and not the other log files, that will sometimes lead me to a possible network or PC specific problem.

Overview ofthe PowerShell Scripts

First we look at OpenConnQueryCloseConn.ps1. The script starts by setting your connection parameters. Make sure you change the items below.

 Here are the connection and query parameters.

$serverName="Your server name"
$databaseName="The name of your database"
$uid="User ID"
$pwd="user Password "
$sqlCommand="A simple select statement like  ---   select count(*) from ITEM"
$logLoaction="This is where you want your log file to write out to.   ---  c:\ps\OpenConnQueryCloseConn.log"

At the bottom of the script is the main loop.

This function (fn_HitSQL) is where the bulk of the work is done. This opens a connection to the SQL Server instance and then tries to execute a query. There are numerous places where the work being done is logged.

There is also a function to log connections and errors. This writes the message along with the date and time to the console and adds it to the log file.

Below is an example of the log file output when there was a network related error.

The second script OpenConnLoopQuery_Long.ps1 is basically the same with an extra loop statement around the part where we run the execute command, so I will not go into details on this script. Download the two scripts and check them out.

Below are some commands that you can use to run the two different PowerShell scripts. 

PowerShell.exe -ExecutionPolicy Bypass -File C:\PS\OpenConnLoopQuery_Long.ps1

PowerShell.exe -ExecutionPolicy Bypass -File C:\PS\OpenConnQueryCloseConn.ps1

Don’t forget to set the the directory location. “C:\PS”

I hope this script helps with your SQL connection issues.

Enjoy!