BlocksTracer is a utility that monitors and captures SQL Server’s blocking locks that occur on Microsoft SQL Server 2005 and Microsoft SQL Server 2008.
BlocksTracer provides an insight to this crucial information at real time and can send a notification email as well as log the data to a file and a database table.
BlocksTracer helps improve application response time by putting a spot light on problematic areas of the database at no time.
BlocksTracer is also used as a diagnostic tool helping to troubleshoot blocking and concurrency issues.
Freeware edition - available in the form of a Console Application
Standard edition - available in the form of a Console Application
Enterprise edition - available in the form of a Console Application and as a Windows Service
| Feature | Comment | Freeware | Standard | Enterprise |
|---|---|---|---|---|
| Multiple Servers | Monitor unlimited number of servers | x | x | OK |
| Windows Service | More reliable for production environments | x | x | OK |
| Email Notification | Every block detected can be notified by mail | x | OK | OK |
| Blocking history file | Every block detected is written to the log file | x | OK | OK |
| Blocking history database | Every block detected is written to database | x | OK | OK |
| Console Notification | Every block detected is displayed at the Console | OK | OK | OK |
If you would like to work with the fully featured Standard Edition or Enterprise Edition then all that is required is to purchase a license. There is no need to uninstall BlocksTracer but only to locate the license file at the working folder.
To obtain a license file follow these steps:
1. Go to www.sqlserverutilities.com/SQL-Server-Utilities-BlocksTracer-download.htm
2. Purchase a license by clicking the PayPal button
3. Email the ComputerName.tek file (located at your working folder) to [email protected]
4. Receive a reply mail containing the license file
5. Locate the received license file at your working folder
The working folder is the folder where BlocksTracer is installed. If the default installation path was not modified during the installation then the path should be:
C:\Program Files\BlocksTracer\ (32 bits machines)
C:\Program Files (x86)\BlocksTracer\ (64 bits machines)
BlocksTracer requires Microsoft .NET Framework version 3.5 installed on the machine where the program is installed.
SQL Server’s port (typically port 1433) open two directions between the Management Console and the SQL Server(s) being monitored.
Windows Server 2000
Windows Server 2003
Windows Server 2008
Windows XP
Windows Vista
The account that BlocksTracer uses to connect to the database requires the ALTER TRACE permissions.
(GRANT ALTER TRACE TO [DomainName\UserName]);
BlocksTracer terminates with a return value of 0 on success and 1 on failure.
BlocksTracer does not take care of the data that has been logged at the database table so over time the table can grow large. You can schedule a job to delete data older than a specific time. If tempdb is used then blocking history will be cleared upon a restart of the SQL Server service on the machine installed with the Management repository.
Run BlocksTracer.msi file to install the program.
Following the installation edit BlocksTracer.exe.config file and Servers.config file.
Once the two files have been configured you can launch the program by running BlocksTracer.exe file.
There are two configuration files to edit before running BlocksTracer.exe:
BlocksTracer.exe.config (see the bellow table for details)
Servers.config (see the bellow table for details)
BlocksTracer.exe.config holds configuration settings related to the Management Console that are common for all servers being monitored such as the mail server address.
Servers.config holds configuration settings related to the SQL Server(s) being monitored.
In case of Enterprise edition when more than a single server needs to be monitored you just have to copy the section that starts and ends with “ServerSettings” and edit it to match the new server you want to monitor. This process has to be repeated once per each server. i.e. a config file that is set to monitor three servers will include this section three times (see the SampleServers.config located at the installation folder).
There are various settings that if required can be modified by editing the file log4net.config.
The key “file” represents the logs file location and defaults to the root of drive C:\ (C:\BlocksTracerLog.log).
The key “maxSizeRollBackups” defaults to 5 which represent the maximum number of log files that are available at a given time. The files are managed in a FIFO (First In First Out) algorithm.
The key “maximumFileSize” defaults to 10 which represent a size of 10MB per log file.
When applying for support please send a description of the problem encountered to [email protected] with BlocksTracer in the subject of the mail and attach the following files:
BlocksTracer.exe.config
BlocksTracer.log
Servers.config
| Key | Value | Description |
|---|---|---|
| ManagmentServer | Host name or IP Address | The SQL Server holding the database logging the notifications |
| ManagmentServerDatabase | Database name | The database logging the notifications. You may use an existing user or system database. If the value configured is of a database that does not exist BlocksTracer will create the database |
| ManagmentServerAuthenticationMode | true / false | Use true for Windows authentication |
| ManagmentServerSQLUser | SQL login | Omitted if Windows authentication is true |
| ManagmentServerSQLPassword | SQL password | Omitted if Windows authentication is true |
| SmtpServer | Host name or IP Address | Mail server |
| SmtpSubject | Notification mail subject | The subject that will appear in the Notification mail |
| Key | Value | Description |
|---|---|---|
| SmtpToList | The Notification mail recipients addresses | A semi column (;) separated list of mail recipients |
| SendMail | true / false | Use true for mail to be sent |
| SQLServer | Host name or IP Address | The SQL Server(s) to be monitored |
| AuthenticationMode | true / false | Use true for Windows authentication |
| SQLUser | SQL login | Omitted if Windows authentication is true |
| SQLPassword | SQL password | Omitted if Windows authentication is true |
| BlockedProcessReportThreshold | 10 | An integer value in seconds that SQL Server will raise an event if there is a blocking lock. 5 seconds is the smallest value that can be used |
| NotificationInterval | 60 | An integer value in seconds for an email to be sent if there is a blocking lock |
| WriteToConsole | true / false | Use true for blocking information to be displayed at the console |
| LogSeverity | true / false | Use true for the blocking information to be written to the log file (BlocksTracerLog.log) |
| ConsoleForeGroundColour | DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magentam, Yellow, White | Set the font color of the blocking information |