SYMPTOMS
You can use the sp_droptask system procedure to remove all tasks assigned
to a given login name. However, if you delete over 100 tasks
simultaneously, you will receive the following warning message 21003:
Error executing xp_schedulersignal extended stored procedure: Trigger
buffer count exceeded.
The following scripts demonstrate the problem:
use msdb
go
/* Insert 101 tasks. */
declare @count int
declare @taskname varchar(10)
select @count = 0
while @count < 101
begin
select @taskname = "task" + convert(varchar, @count)
exec msdb..sp_addtask @taskname, @subsystem = 'TSQL', @server = null,
@username = 'sa', @databasename = 'msdb', @enabled = 1,
@freqtype = 2, @freqinterval = 0, @freqsubtype = 1,
@freqsubinterval = 0, @freqrelativeinterval = 1,
@freqrecurrencefactor = 1, @activestartdate = 19970708,
@activeenddate = 99991231, @activestarttimeofday = 0,
@activeendtimeofday = 235959, @runpriority = 0,
@emailoperatorname = null, @retryattempts = 0,
@retrydelay = 0, @loghistcompletionlevel = 2,
@emailcompletionlevel = 0, @command = 'sp_who',
@tagadditionalinfo = null, @description = null,
@tagobjectid = 0, @tagobjecttype = 0, @cmdexecsuccesscode = 0
select @count = @count + 1
end
/* Drop all tasks. */
sp_droptask null, sa