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

aware.

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

developer.

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

the environment.

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

   for
   select spid
   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.