April 18, 2011 at 9:39 pm
Hi there,
We've had a linked server to db2 since last year and have only recently experienced hanging jobs. The job runs a stored procas which selects data from DB2 and imports into Sql Server. However, when scheduled as a job intermittently hangs. SQL Logs, Agent Logs don't help with any hints.
We've tried dbcc's space, memory, Nic card best practices, amd still receiving same issues, daily.
Has anyone ever encountered this?
¤ §unshine ¤
April 19, 2011 at 1:47 am
sunshine-587009 (4/18/2011)
Hi there,We've had a linked server to db2 since last year and have only recently experienced hanging jobs. The job runs a stored procas which selects data from DB2 and imports into Sql Server. However, when scheduled as a job intermittently hangs. SQL Logs, Agent Logs don't help with any hints.
We've tried dbcc's space, memory, Nic card best practices, amd still receiving same issues, daily.
Has anyone ever encountered this?
What sort of syntax do you use for this? I have seen few developers feeling bad about the high response times of queries that fetch remote data.
They were using a 4-part name like select * from [Remote Server].database.schema.table
I advised them to use open query syntax and performance wise it looked better and faster. I am wondering how are you using them.Can you elaborate a little.
April 19, 2011 at 2:24 am
For imports, you can even use Import Export wizard which you can schedule to run as an SSIS package and their performance will be better than using linked servers.
April 19, 2011 at 7:58 am
We are already using OpenQuery. We've also noticed that when multiple jobs run using the same linked server name, it hangs, we've had to adjust timing on these b ut some are just inevitable and needed for the business. Creating SSIS packages is a great idea, however, this is production issue, and we currently do not have time to develop test and migrate them. They are currently running as job calls to stored procedures. Is there a maximum amount of connections that linked server allows? We do have the default for remote, but I"m not sure this would be the same regarding linked servers. We are using SQL Server 2005 sp3. Any help appreciated.
¤ §unshine ¤
April 29, 2011 at 10:36 am
Turns out after several conference calls and temp fixes it was the Data Direct Driver that needed to be updated for 64 bit servers. In the mean time I found and updated a proc found online to notify us of long running jobs on this particular instance. Here is the code in case you ever need it! It could be improved to add how long it's been running or gy by an overage of average runtime, but I haven't had the time. Thank you for your help!
--Code for fn_hex_to_char function:
use msdb
go
CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
-- Written by: Gregory A. Larsen
-- updated by Shirley Noa to use minutes, sql 2005 code and email
-- Date: May 25, 2004
-- Description: This function will take any binary value and return
-- the hex value as a character representation.
-- In order to use this function you need to pass the
-- binary hex value and the number of bytes you want to
-- convert.
begin
declare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s-2 varchar(100)
declare @h varchar(100)
declare @j-2 int
set @j-2 = 0
set @h = ''
-- process all bytes
while @j-2 < @l
begin
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s-2 = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s-2 = cast(substring(@digits,@i%16+1,1) as char(1)) + @s-2
-- build string of hex characters
end
return(@h)
end
--Code for usp_log_running_jobs SP:
CREATE proc p_long_running_jobs as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This stored procedure will detect long running jobs.
-- A long running job is defined as a job that has
-- been running over 6 hours. If it detects any long
-- running job then an email is sent to the DBA's.
------------------
-- Begin Section 1
------------------
set nocount on
declare @C char(1000)
declare @cnt int
-- Create table to hold job information
create table #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
------------------
-- Begin Section 2
------------------
-- create a table to hold job_id and the job_id in hex character format
create table ##jobs (job_id uniqueidentifier ,
job_id_char varchar(100))
-- Get a list of jobs
insert into #enum_job
execute master.dbo.xp_sqlagent_enum_jobs 1,
'garbage' -- doesn't seem to matter what you put here
------------------
-- Begin Section 3
------------------
-- calculate the #jobs table with job_id's
-- and their hex character representation
insert into ##jobs
select job_id, dba.dbo.fn_hex_to_char(job_id,16) from #enum_job
------------------
-- Begin Section 4
------------------
-- get a count or long running jobs
select @cnt = count(*)
from master.dbo.sysprocesses a
join ##jobs b
on substring(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c on b.job_id = c.job_id
-- check for jobs that have been running longer that 6 minutes.
where login_time < dateadd(mm,-6,getdate())
------------------
-- Begin Section 5
------------------
if @cnt > 0
-- Here are the long running jobs
exec master.dbo.xp_sendmail
@recipients='EmailName@DomainName.com',
@subject='Long Running Jobs over 6 minutes',
@query= 'select substring(c.name,1,78)
''These jobs have been running longer than 6 minutes''
from master.dbo.sysprocesses a
join ##jobs b
on substring(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c on b.job_id = c.job_id
where login_time < dateadd(mm,-6,getdate())'
drop table #enum_job
drop table ##jobs
GO
¤ §unshine ¤
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply