The “Zombie” Modules
Jahil has posted a nice intro to Zombie processes (that most of unix users come across in routine).
The same situation often occurs, in software development; especially in product development where different modules sometimes are re-written/replaced with new implementations and old code continue to exist in text as well as binary forms. People use different techniques to solve issues like when and how to remove such “zombie” modules. Here is what I have adopted for the database part.
Setting Things Up
I always create a table in each database with name, Activities, (or ProcedureCalls, in case Activity is already some entity), and its associated procedure, ActivityAdd or ProcedureCallAdd (I try to prefix procedures with main entities, so that procedures related with any given entity are shown together when viewing in Enterprise Manager or Visual Studio. The table has the fields for procedure name, who called it, how many times it’s been called, when its last time called, and when its first time called. Here is the relevant portion from the ActivityAdd procedure that explains the concept itself.
if exists (select tableindex from activity where procedurename=@procname and calledby=@calledBy)
update activity set accesscount=accesscount+1, lastupdated=getdate(), calledby=@calledby where procedurename=@procname;
insert into activity (createdate, procedurename, calledby, accesscount, lastupdated) values (getdate(), @procname, @calledby, 1, getdate());
Then in each stored procedure I call this procedure!
An interesting thing: I always create parameters of each such exposed procedure for Application name and its version. Here is such procedure:
CREATE procedure dbo.SignIn
@AppName nvarchar(20) = null
@AppVersion nvarchar(20) = null,
@UserID nvarchar(20) = null,
@Password nvarchar(20) = null
set nocount on;
--raiserror('Service not available', 16, 1);
if @appname is null or @appversion is null or @userid is null or @password is null
raiserror('Missing parameter', 16, 1);
declare @calledBy nvarchar(50);
set @calledBy = @appname + @appVersion;
exec ActivityAdd @procName = 'SignIn', @calledBy = @calledby;
--Do the processing
Wishing, that we can pass parameters out of band, so that we can have nice interfaces for exposed procedures! Yes, we can pass application information using client libraries, but there are many other scenarios (the stateless server/client) where we need to pass certain (state) information in each call.
Now any time down the road, you can easily query the activity table to know the “zombie” procedures. Don’t forget to verify that the procedure you are going to delete is no longer in use using this Activity table.