Brut force spid management
Removing active user jobs from a MS SQL
Server 7 database.
In SQL Server, a "system process Id" or spid uniquely identifies each active
process. This spid is actually an identifier column of the master..sysprocesses
table (though sysprocesses has no defined keys or indexes). You can access a
sysprocesses' spid many ways. You could directly SELECT from sysprocesses
(though it is never recommended to directly access system tables procedurally),
or you could execute the sp_who or sp_who2 system stored procedures, or you
could ascertain the spid of the current process by examining the global variable
@@SPID. While it could seem useful to access the spid under normal production
processing, I would suggest that such explicit implementation of the underlying
architecture could yield a less than robust application.
There are enough challenges managing contention in userbases and temdb
without adding master database blocking to the mix. The evolution of
parallelism, pooling, and process granularity (threads and fibers) -- along with
a host of other issues -- expose vulnerabilities in application level actions
based upon explicit references to a sysprocesses spid. On the other hand, it is
often an imperative that administrative and maintenance processes are spid
A data conversion or DDL script can become blocked by an active spid that is
already using an 'object' acted upon by the script. A database restore can be
blocked by an active spid in the database. In general, you can tell what any
active process is up to if you know its spid. The possible uses for spid related
information is bounded only by the needs and creativity of an administrator or
In a development environment, it is typical to recreate the databases
frequently. This might be done each night from fresh copies of SourceSafe
projects or on demand based on development needs. In any case it is necessary to
end all active processes in a database in order to drop the existing playground.
One could simply stop and start the SQL Server or the OS. The potential problem
here is disruption of other important processes running on the box at the time
the service or computer is recycled. One could simply issue a SQL Server kill on
each process using the database. Furthermore, automating the process can save
time and allow the process to execute unattended.
In the admin subsystem it is useful to have the ability to remove all users
from any database. The approach taken here is to generate and compile a stored
procedure for each database to be administered in the distributed environment.
The procedures are then evoked from the admin server and all user level spids
are removed from the database on any target server by issuing kills from a
cursor in this generated stored procedure.
If there is a likelihood of a user getting in the database before the
administrative task that needs the resource is invoked, it could be advantageous
to further restrict access. In a well-disciplined environment, the "dbo use
only" database option might be enough security. There are many scenarios for
this concurrency issue and the solution will be best determined specifically for
Zombies seem to occur much less often with SQL 7 than in previous releases,
but they do still occur. Most zombies are the result of bad code or poor design.
A Zombie is a process visible in sysprocesses but not executing. The Zombie
can't be killed because it never executes the request to end. To get rid of
zombies stop and start SQL Server. Most zombies do not inhibit the ability to
gain exclusive use of an object. A few will.
To generate spid killing procedures, first compile the MakeSpidKiller stored procedure into the admin
database. Then, whenever a database specific spid killer is needed, execute
MakeSpidKiller providing the server name and database name of the target
database. The MakeSpidKiller will compile a stored procedure into the admin
database named as ExpungeUsers_[server name]_[database name] (example: ExpungeUsers_bwunder_Pubs)
Custom Spid Killer
Situations may arise requiring a granularity other
than 'all users in the database'. For these situations modify the where clause
of the dbuserscursor
. For example, if the desired result is to terminate
only jobs owned by a specific domain user use:
declare dbuserscursor cursor
from ' + @pServerName + '.master.dbo.sysprocesses p
inner join ' + @pServerName + '.master.dbo.sysdatabases d
on p.dbid = d.dbid
where d.name like ''' + @pDbName + '''
and p.nt_username = 'bill'
and p.nt_domain = 'sales'
As with all tools in the admin subsystem, modify your spid killer to do what
you need it to do. Getting rid of all users in a database works for an
environment that needs to drop and recreate a database in the nightly batch. For
other environments it may be overkill, for others it may be inadequate.