I'm currently working on an Internet project, and every time we deploy it, the process is supposed to remove all existing timer jobs and reinstall them. However, because of a bug we haven't fixed yet, the removal of the timer jobs doesn't work, but the reinstall does — so we were getting hundreds of extra (duplicate) timer jobs created in our SharePoint environment. This causes problems of its own, because the timer jobs all run at once, and start contending with each other to do the same work.

There is an STSADM command (DeleteConfigurationObject) which will remove objects of whatever type you like. However, you need to provide the GUID of the objects, one at a time. Looking up the GUID for hundreds of duplicate timer jobs, and plugging them into STSADM might just be a bit time-consuming, right?

So as an old database guy, I figured there had to be an easier (and SQL-based) way to clean these critters up. The first step is to figure out where in the SharePoint database they are stored. I knew from looking in SharePoint Central Administration that timer jobs were identified by GUIDs. I wrote a query to generate a select statement against every table which had one or more GUID columns, looking for the specific GUID that I found in Central Admin.

This is the query:

select 'select * from dbo.' + tables.name + ' where ' + columns.name + ' = ''12345678-90AB-CDEF-1234-1234567890ABCD'''
from sys.objects as tables
inner join sys.columns as columns
  on tables.object_id = columns.object_id
where columns.system_type_id = 36               -- type "36" represents a GUID
and  tables.Type = 'U'                          -- "U" represents a user table

Substitute the GUID of one of the jobs you want to find instead of the one above in my query.

Run this query (try all the SharePoint-related databases, unless you know specifically where you want to look!) and it will generate a list of select statements. You then cut and paste those statements into a new query window, run them 50-100 at a time, and see which ones return any data. 

Eventually, I found that the dbo.Objects table in our MOSS_Config database is the one that holds the timer job info. A little more poking at individual rows shows that the "Name" column contains the user-specified name of the timer job. In our case, these started with either "generate_sitemap" or "stock_quote".

The next step is to run a query which will return all of the GUIDs of the rows which represent the timer job(s) you are looking for. While you're at it, you may as well format the returned rows as an STSADM command. This is the query to do it:

select 'stsadm -o deleteconfigurationobject -id ' + convert (varchar(50), id) from dbo.objects 
where name like 'generate_sitemap%' or name like 'stock_quote%'

Run this query. Cut and paste the output into a standard text file, and save it as a *.bat file. You can now execute the batch file, and it will repeatedly invoke STSADM to delete the objects you want to clobber.

Easy, huh?  :-)