Brut force spid management
September 7, 1999
Brut force spid managementRemoving 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 KillerSituations 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.