November 5, 2007 at 5:54 am
I have a lot of jobs all called at the same time running the same sp with different parameters i am getting strange error which appears to be due to mixing of the temp tables created in the sp accross different calls.
I created to script below to demonstrate that two scheduled jobs called a the same time will use the same SPID. Therefore how is it possible to ensure consistent use of Temp tables with the same name.
so after the jobs have been running you should see that The first job and the second job called at exactly same time have the same SPID.....
Any thoughts?
Thanks,
Jules
---------------------------------------------------------------------
-----------Script proves Jobs called at same time run under same SPID--
----------------------------------------------------------------------
---SCRIPT CREATEs ALL OBJECTS IN DB CALLED DBA
--create database DBA
create table SpidLog ------------Table to Hold record Of spids used to run JObs
(SPID int, Parameter varchar(100), Datetime_dt datetime)
go
create proc dba_WriteSpid (@p varchar(100))-- SP called by JOB to record SPIDs
as
insert SpidLog
select @@spid, @p, getdate()
go
BEGIN TRANSACTION --Creates two jobs running every minute which each enter their spid in to the --SpidLog table.
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DBA_TESTSecond')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DBA_TESTSecond'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'DBA_TESTSecond'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DBA_TESTSecond', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'DBA_TEST', @command = N'exec dba_WriteSpid ''Second''', @database_name = N'DBA', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'DBA_TESTSecond', @enabled = 1, @freq_type = 4, @active_start_date = 20071105, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
------------------------------------------------------------
November 8, 2007 at 8:17 am
November 8, 2007 at 9:37 am
Two different jobs, even with the same user calling them, in this case SQLAgent, should have different SPIDs. The SPID is the process handle for the connection. Name of user doesn't matter.
I'm not sure this is a common issue. A SPID should only be running one process at a time, so it should have it's own table in tempdb. Are you adding a global temp table (##table) or a local one?
November 8, 2007 at 9:44 am
try the script i provided above. Should demonstrate what i mean.
Doesnt use temp table just demonstrates two jobs running at same time using the same spid.
Obviously the problem occurs once you add in temp tables But this script should make clear what i am getting at.
The two jobs run every minute just populating a table with the job name and the spid and the date time.
run the script wait for a few minutes then have a look in the spidlog table
Script assumes there is DB named 'DBA'.
Thanks for your help.
November 9, 2007 at 12:31 am
Spids get reused. If your job completes and disconnects, another job connects a second later, there's a chance of it running under the same SPID as the first job. It is however a different conneection and temp tables (unless they are global) don't persist.
I believe that SQL allocates a new connection the lowest unused SPID that is greater than 50. So if you have lots of long-lasting connections from the application, but your job disconnects and reconnects frequently, it may get the same SPID.
Can you post the procedure that you mentioned in your original post, the one with the temp table problems please?
The code you posted only creates a single job. I created a second with exactly the same schedule and let them run for a few minutes. I got the following.
SPIDParameterDatetime_dt
55First2007-11-09 09:32:00.983
56Second2007-11-09 09:32:00.983
56First2007-11-09 09:33:00.987
55Second2007-11-09 09:33:00.987
56First2007-11-09 09:34:01.003
55Second2007-11-09 09:34:01.003
56First2007-11-09 09:35:01.007
55Second2007-11-09 09:35:01.007
57First2007-11-09 09:36:00.023
56Second2007-11-09 09:36:00.023
58First2007-11-09 09:37:00.023
59Second2007-11-09 09:37:00.023
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply