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.
MSSQL blocks
and orphan finder
Benefits: MSSQL Blocks helps you determine
which SQL Server instances in your organization
are having locking and blocking problems. Orphan
Finder helps you find records in your SQL Server
2005 databases that have values in a foreign key
column that don’t exist in the parent table.
System Requirements and Notes: MSSQL
Blocks requires SQL Server 2005, 2000. Orphan
Finder requires SQL Server 2005 and the Microsoft
.NET Framework.
How to Get It: You can download MSSQL Blocks
from sqlblocks.narod.ru. You can download Orphan
Finder from www.spi.hr/ZaITprofesionalce/OrphanFinder/tabid/356/Default.aspx. |
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.
End of Article
