MSSQL Blocks and Orphan Finder

Typically, I discuss only one free tool each month in my Tool Time column. However, I have two free single-purpose utilities to discuss this month. MSSQL Blocks, which was written by Konstantin Knyazev, a SQL Server and Delphi enthusiast living in Moscow, makes it easy to spot locking and blocking problems across multiple SQL Server instances. Orphan Finder, which was written by Miljen Stankovic, a multicertified SQL Server DBA and development manager working in Zagreb, Croatia, finds foreign key columns that don’t have a matching value in the parent key. Let’s take a look at what each of these tools does in more detail.

MSSQL Blocks

It can be difficult to use SQL Server’s native tools to determine which server in a server farm might be having blocking problems because SQL Server’s native tools work on only one server at a time. However, MSSQL Blocks makes it quick and easy to determine which SQL Server instances are having blocking problems. MSSQL Blocks is a Delphi-based utility that collects information (via ADO in asynchronous mode) about blocked processes from multiple SQL Server 2005 and 2000 instances, and presents the information in an intuitive format. This utility is especially useful if your environment includes applications that have transactions that span multiple SQL Server instances. The format of information retrieved in MSSQL Blocks is based on the server ID and the server process ID (SPID). The tool also reports the wait state, which describes the type of lock held by the SPID, in the Wait Info column of the MSSQL Blocks console, as shown in Figure 1.

In addition to reporting the current locking and blocking activity on all the monitored servers, MSSQL Blocks also stores the history of blocking activity on the SQL Server instances in an XML file. This history can be very helpful if you want to analyze blocking trends and behaviors on one or more servers over a long period of time.

Orphan Finder

This simple GUI tool helps you identify records in a SQL Server 2005 database that have values in a foreign key column that don’t exist in the parent table. Orphan Finder can be especially useful when analyzing data that originated in legacy systems or in systems that lack rigorous data-cleansing capabilities.

To use Orphan Finder, enter the connection details of the SQL Server instance and database that you want to analyze. Note that you must know the name of the database before you attempt to connect to it because there’s no drop-down list of database names to choose from. Once you connect to the SQL Server database, Orphan Finder analyzes the database and displays all the database tables and their foreign keys in a tree structure. Errors in data quality are highlighted in red in the report, as shown in Figure 2. To see all the database records that don’t have a parent record, double-click the name of the foreign key containing the error and Orphan Finder will expand the details to show all the records containing errors.

System Requirements

MSSQL Blocks is a standalone product that can connect to SQL Server 2005 and 2000 and requires the client connection tools that ship with SQL Server Management Studio (SSMS). You can download MSSQL Blocks from sqlblocks .narod.ru. Konstantin also supports the tool via the MSSQL Blocks Google group at groups.google.com/group/sql-blocks.

Orphan Finder is a Microsoft .NET application that currently connects to only SQL Server 2005 databases. It’s available for download from www.spi.hr/ZaITprofesionalce/OrphanFinder/tabid/356/Default.aspx, where Miljen supports the tool directly through email.