DTS Intermittently hangs !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Intermittently hangs !!!

Author

Topic

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-05 : 21:36:35

Has anyone experienced problems with DTSRUN in a job whereby the job just hangs for hours until you kill it? Any ideas why this could be occurring please?

SQL Server 2000 DTS package:
SQL database (source)
2 SSAS olap cubes databases (destinations) - some of which have fact tables based off views (not tables).

4 DTS packages:
* 2 x REFRESH cubes:
- schedule runs twice a day (eg: 6:30am/pm)
- 2 SSAS tasks: incremental update all dimensions -> (on success) refresh all cubes
* 2 x FULL proc cubes: 2 SSAS tasks:
- schedule runs once/day (1am)
- full process all dimensions -> (on success) full process all cubes

A full process (olap analysis cubes) dts will intermittently hang until it is killed (eg: still running after 15hrs instead of completing whithin 1hr). Whether in an 'OS command' type job step or in a 'transact-sql' type job step using 'master.dbo.xp_cmdshell' to run 'DTSRUN'. If it is running via xp_cmdshell then cant just kill the job as the command run from xp_cmdshell will continue running until it's complete (but it never completes when it's hanging), have to kill SQL Server instead.

Any ideas why the DTS could hang please?

The below job setup is now on 7 different servers & the remaining 2 servers still run DTSRUN using OS command job (instead of tsql) to process the cubes.

I have an SQL server agent job with 9 job steps in it (7 steps shown below), step 7 is the main one I've noticed hanging (intermittently):


----------------------------------------
-- 
-- ============================================
-- JOB STEP 1: Update 'hist' tables (job steps 2 & 3 are similar)
-- ============================================
 
-- Initially set errcode to 0 for ALL steps
update [scheduler] set [errcode] = 0

declare @result int

exec @result = master.dbo.xp_cmdshell 'C:\some.exe C:\someddb.mdb'
update [Scheduler] set [errcode] = @result, [value] = 0 where [key] = 'Upload hist data'

if (@result = 0)
   print 'Success'
else
begin
   print 'Failure: error code=' + CONVERT( char(8), @result )
   RAISERROR('Upload hist data failed.', 11, 1)
end
----------------------------------------
-- 
-- ============================================
-- JOB STEP 4: Refresh 24hr Cubes  (every 10-20mins)
-- ============================================
 
declare @result int
 
exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
update Scheduler set [errcode] = @result, [value] = 0 where [key] = 'Refresh 24hr Cubes'

if (@result = 0)
   print 'Success'
else
begin
   print 'Failure: error code=' + CONVERT( char(8), @result )
   RAISERROR('24hr Cubes failed to refresh.', 11, 1)
end

----------------------------------------
-- 
-- ============================================
-- JOB STEP 5: FULL Process 24hr Cubes IF required  (1am)
-- ============================================
 
declare @result int
declare @processcubes int
 
set @processcubes = ( select [value] from Scheduler where [key] = 'FULL Process 24hr Cubes' )

if @processcubes = 1
begin
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update Scheduler set [errcode] = @result where [key] = 'FULL Process 24hr Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update [Scheduler] set [value] = 0 where [key] = 'FULL Process 24hr Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('24hr Cubes failed to FULL process.', 11, 1)
  end
end
else  
  print '24hr Cubes not scheduled to be FULL processed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 6: Refresh Cubes IF required  (6:35am/pm)
-- ============================================
 
declare @result int
declare @refreshcubes int
 
set @refreshcubes = ( select [value] from Scheduler where [key] = 'Refresh Cubes' )
 
if @refreshcubes = 1
begin
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update Scheduler set [errcode] = @result where [key] = 'Refresh Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update [Scheduler] set [value] = 0 where [key] = 'Refresh Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('Cubes failed to refresh.', 11, 1)
  end
end
else  
  print 'Cubes not scheduled to be refreshed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 7: FULL Process Cubes IF required  (1am)
-- ============================================
-- Select 'tempdb' database for this job so SHRINKfile commands work on that database.
-- Note: hardcoded 'thedb..Scheduler' used as 'tempdb' selected.
 
declare @result int
declare @processcubes int
 
set @processcubes = ( select [value] from thedb..Scheduler where [key] = 'FULL Process Cubes' )
 
if @processcubes = 1
begin
  print 'Shrink tempdb - tempdev: ' + RIGHT( GETDATE(),7 )
  DBCC SHRINKfile ('tempdev') WITH NO_INFOMSGS
  print 'Shrink tempdb - templog: ' + RIGHT( GETDATE(),7 )
  DBCC SHRINKfile ('templog') WITH NO_INFOMSGS
  
  print 'FULL process cubes: ' + RIGHT( GETDATE(),7 )
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update thedb..Scheduler set [errcode] = @result where [key] = 'FULL Process Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update thedb..Scheduler set [value] = 0 where [key] = 'FULL Process Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('Cubes failed to FULL process.', 11, 1)
  end
end
else  
  print 'Cubes not scheduled to be FULL processed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 8: Reindex Tables IF required  (SUN 12am)
-- ============================================

declare @result int  
declare @reindextables int

set @result = 0
set @reindextables = ( select [value] from Scheduler where [key] = 'Reindex Tables' )
 
if @reindextables = 1
begin
  -- for now, turn flags off as assume job will SUCCEED - if FAILS, turn flag back on
  update Scheduler set [errcode] = 0, [value] = 0 where [key] = 'Reindex Tables'

  DECLARE @TableName varchar(255)
  DECLARE TableCursor CURSOR FOR
	SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

  OPEN TableCursor
  FETCH NEXT FROM TableCursor INTO @TableName
  WHILE @@FETCH_STATUS = 0
  BEGIN 
    IF LEFT(@TableName,5) = 'hist_'
       PRINT @TableName + ': ' + RIGHT( GETDATE(),7 )
    DBCC DBREINDEX(@TableName, ' ', 100) WITH NO_INFOMSGS
    SELECT @result = @@error

    -- DBCC caused an error, set errcode (if it's < @result)
    IF @result <> 0
    BEGIN
      print 'Failure: error code=' + CONVERT( char(8), @result ) + @TableName
      -- turn flag back on as FAILED
      update Scheduler set [value] = 1 where [key] = 'Reindex Tables'
      -- set errcode to highest failure error code returned
      update Scheduler set [errcode] = @result where [key] = 'Reindex Tables' and [errcode] < @result
    END

    FETCH NEXT FROM TableCursor INTO @TableName

  END
  CLOSE TableCursor
  DEALLOCATE TableCursor

end
else  
  print 'Tables not scheduled to be reindexed.'
----------------------------------------
-- 
-- ============================================
-- JOB STEP 9: Return Success / Error Code
-- ============================================

declare @worst_result int
declare @error_count varchar(2)
declare @step_count varchar(2)

set @worst_result = (select max(errcode) from Scheduler)
set @error_count = cast((select count(*) from Scheduler where errcode > 0) as varchar(2))
set @step_count = cast((select count(*) from Scheduler) as varchar(2))

if @worst_result > 0 
begin
  PRINT 'OVERALL FAILURE:  PowerView Upload Job had ' + @error_count + ' failed step(s) out of ' + @step_count + '!'
  RAISERROR('Upload Job had failed step(s)!', 11, 1)
  -- syntax error near +
  -- RAISERROR('Upload Job had ' + @error_count + ' failed step(s)!', 11, 1)
end
else
   PRINT 'OVERALL SUCCESS: ALL steps completed successfully.'
----------------------------------------

Need to find SPID for a DTS Package to kill it please if exceeds certain time (eg: 4hrs or more - as assume hung)?

So I can kill it using something like this:


KILL 54;
KILL 54 WITH STATUSONLY;
GO

Could changing these 2 values in 'sp_configure' help?
remote login timeout (s) and/or remote query timeout (s)
currently set to 2,147,483,647secs = 596,523hrs = 24,855days = 68yrs

Also, how can I match up sysdtspackagelog with sysprocesses for example?


SELECT [name], starttime, endtime, datediff(ss, starttime, getdate() )/3600.0 as DTS_RUNHRS
FROM sysdtspackagelog 
where endtime is NULL
 --and datediff(ss, starttime, getdate() )/3600.0 >= 4
ORDER BY starttime DESC

name Refresh All Cubes
starttime 2009-12-02 13:35:32.000
endtime NULL
DTS_RUNHRS 0.061111


select spid, kpid, dbid, status, cpu, cpu/3600.0 as RUNHRS, program_name, login_time, cmd, sql_handle
from master.dbo.sysprocesses
where spid>=50 and dbid>0
--	and cpu >= (3600)
and [program_name] = 'Microsoft SQL Server Analysis Services'
order by spid

spid 59
kpid 2736
dbid 5
status runnable
cpu 469062
[COLOR="Red"]RUNHRS 130.295 -- this cant be right???[/COLOR]
program_name Microsoft SQL Server Analysis Services
login_time 2009-12-02 13:29:24.703
cmd SELECT
sql_handle 0x01000500FB024F33C020E8640000000000000000

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-05 : 23:16:12

Most likely you have some blocking. I'll check for that 1st.

By the way...I wouldn't allow xp_cmdshell...instead use step type of OS Command.

To kill it, you need to kill it on both servers (if it touches another server) and if not, kill it from the OS.

Can always find spid with sp_who2.

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-07 : 00:16:44

Hi Russell

As a workaround/temp measure, I am trying to implement a scheduled job to check if a DTS has been running for a long time (eg: > 5hrs) & if it has then kill it off & would like to also write some debugging info to file, like in the below script...

The DTSRUN is used via xp_cmdshell as need to do TSQL commands before & after it & update a (Scheduler) table in the database... per the code I posted above.

Everything is on the (local) server, so was trying to find the DTS SPID so I can issue the kill command in the scheduled job. I can't see how to find the DTS SPID other than to just guess/assume that it is a job running for 'SQL Server Analysis Service' & what happens when there's more than 1 running? Would a process also appear if someone is accessing the cubes? :)

Cant quite get the following script to work properly at moment...

Error: 'Invalid length parameter passed to the substring function.' ?

If I comment out the 'left outer join .... sysdtspackages' line the error no longer occurs. If js.command doesn't contain 'dtsrun' it seems to cause the error. Maybe I need a different join type?
-----


ALTER   PROCEDURE GetCurrentJobInfo AS

SELECT   p.SPID,
        Blocked_By = p.Blocked,
        p.Status,
        p.LogiName,
        p.HostName,
        Program = coalesce('Job: ' + j.name, p.program_name),
        p.program_name,
        job_name = coalesce(j.[name], ''),
        jobstep_id = coalesce(js.[step_id], ''),
        jobstep_name = coalesce(js.[step_name], ''),
             js.[command],
        dts_name = coalesce(d.name, ''),
        DBName = db_name(p.dbid), 
        Command = p.cmd,
        CPUTime = p.cpu,
        DiskIO = p.physical_io,
        LastBatch = p.Last_Batch,
--      LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ),  -- SQL Server 2005+
--      LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ),  -- SQL Server 2000 ? FAILS
        p.WaitTime,   
        p.LastWaitType,   
        LoginTime = p.Login_Time,   
        RunDate = GetDate(),
        [Server] = serverproperty('machinename'),   
        [Duration(s)] = datediff(second, p.last_batch, getdate())  
FROM master.dbo.sysprocesses p  
        left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100) 
        left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id 
                and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 
        left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')
where p.spid > 50  
--      and p.status <> 'sleeping' 
        and p.spid <> @@spid  
order by p.spid

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-15 : 07:44:27

BUMP.... as no answer yet...

Anyone at all know how to find the SPID for a DTS?
Any feedback on how I can do it in another way to avoid xp_cmdshell?

Trying to run commands in a job to run exe's & DTS's BUT need to check value in a table before running them ('scheduler' table) and after commands finished, update the values in the same table (eg: error returned & turn off flag if succeeded). This is where the need for xp_cmdshell came into it, but can't just cancel a job if it's executing xp_cmdshell as it continues to run until xp_cmdshell has complete (which it never does if the DTS is hanging).

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-17 : 20:34:59

BUMP...

Some of the questions include:
(1) How to find SPID# for a DTS pkg that's running.
(2) how to link dbo.sysprocesses to dbo.sysdtspackages - I can but occasionally my left outerjoin to sysdtspackages returns an error
(3) Why does a DTS pkg to process Analysis server OLAP cubes just hang for hrs sometimes until kill it.
(4) want an alternative to xp_cmdshell as cant stop a job whilst it is running that as it waits for whatever xp_cmdshell is supposed to do to finish before exiting the job.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-18 : 10:47:07

select spid from sysprocesses where ltrim(rtrim(program_name)) = 'DTS Designer'

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-18 : 21:31:41

russell - tried that but didnt return anything...


select count(spid), cmd, program_name
from master.dbo.sysprocesses 
where spid >= 50 and spid <> @@SPID
group by cmd, program_name

* WHEN Job Step RUNNING that runs a DTS pkg:


2	AWAITING COMMAND	.Net SqlClient Data Provider                                                                                                    
2	AWAITING COMMAND	Microsoft SQL Server                                                                                                            
1	AWAITING COMMAND	Microsoft SQL Server Analysis Services                                                                                          
2	AWAITING COMMAND	Microsoft(R) Windows (R) 2000 Operating System                                                                                  
2	AWAITING COMMAND	MS SQLEM                                                                                                                        
1	AWAITING COMMAND	SQLAgent - Alert Engine                                                                                                         
1	AWAITING COMMAND	SQLAgent - Email Logger                                                                                                         
1	AWAITING COMMAND	SQLAgent - Generic Refresher                                                                                                    
1	EXECUTE         	SQLAgent - TSQL JobStep (Job 0x.......... : Step 4)

* WHEN Job Step FINISHED that runs DTS pkg:


3	AWAITING COMMAND	.Net SqlClient Data Provider                                                                                                    
1	AWAITING COMMAND	Microsoft SQL Server Analysis Services                                                                                          
2	AWAITING COMMAND	Microsoft(R) Windows (R) 2000 Operating System                                                                                  
2	AWAITING COMMAND	MS SQLEM                                                                                                                        
1	AWAITING COMMAND	SQLAgent - Alert Engine                                                                                                         
1	AWAITING COMMAND	SQLAgent - Email Logger                                                                                                         
1	AWAITING COMMAND	SQLAgent - Generic Refresher

I can only assume the 'Microsoft SQL Server' and 'Microsoft SQL Server Analysis Services' processes are run from the DTS but how do I know for sure.

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-18 : 21:49:26

Also, when the jobstep with DTS is not running, I sometimes get this, I assume this is because someone is accessing the OLAP Cubes.


select spid, login_time, last_batch, cpu, memusage, cmd, program_name
from master.dbo.sysprocesses 
where program_name like '%sql server%' and spid >=50 AND @@SPID <> spid


61	2009-12-19 10:38:00.853	2009-12-19 10:42:30.337	103891	0	AWAITING COMMAND	Microsoft SQL Server Analysis Services

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-22 : 03:13:39

STILL NO ANSWER to (1), (3) or (4)... anyone please??? :)

Here's the solution to my own question (2)...



-- NOTE: dts_name will ONLY return a value 'IF' DTSRUN used with dtsname.
SELECT  
	p.SPID,
        Blocked_By = p.Blocked,
        p.Status,
        p.LogiName,
        p.HostName,
	p.open_tran,
        Program = coalesce('Job: ' + j.[name], p.program_name),
        p.program_name,
        job_name = coalesce(j.[name], ''),
        jobstep_id = coalesce(js.[step_id], ''),
        jobstep_name = coalesce(js.[step_name], ''),
        js.[command],
        dts_name = coalesce(d.[name], ''),
        DBName = db_name(p.dbid), 
        Command = p.cmd,
        CPUTime = p.cpu,
        DiskIO = p.physical_io,
        LastBatch = p.Last_Batch,
        -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+
        -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS
        p.WaitTime,   
        p.LastWaitType,   
        LoginTime = p.Login_Time,   
        RunDate = GetDate(),
        [Server] = serverproperty('machinename'),   
        [Duration] = datediff(second, p.last_batch, getdate())  
FROM master.dbo.sysprocesses p  
        left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)
        left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 ) 
        left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')
where 
	p.spid > 50  
--      and p.status <> 'sleeping' 
        and p.spid <> @@spid  
order by 
	p.spid

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-22 : 10:50:15

1. The answer I gave you is correct. Not sure why you aren't seeing any results.

3. Could be lots of reasons. How much data is it processing. Most likely you have blocking, or a network issue.

4. As I said earlier, I won't use xp_cmdshell, I'll use a job step type OS Command, and enter my dtsrun statement there. But there's a couple of caveats: 1st, it won't make any difference for trying to kill it. Once you kill it, whatever the dts is in the middle of needs to be rolled back. If you enabled transactions for the package, then everything the package has done to that point needs to be rolled back. This can (and often does) take a long time.

If the dts copies data from one server to another, say from serverA to serverB and you kill the process on serverB, you may still need to go to serverA and kill it there. You basically end up with a disconnected network session, but the SELECT is still running on A.

You need to break this stuff up into seperate job steps and not one TSQL script. Make sure to log output from the DTS itself to a file. This will give you a LOT of information useful for troubleshooting.

Has this ever succeeded, or is this a new problem?

shell_l_d
Starting Member

17 Posts

Posted - 2009-12-22 : 18:35:20

Hi russell

Further to my original post... I am testing on SQL Server 2000 on one local server, not transferring data across/between servers.

(1) When you run your script (below), do you have the DTS pkg open in Design mode maybe?


select spid from sysprocesses where ltrim(rtrim(program_name)) = 'DTS Designer'

Wonder what's different in the way you are testing this script to the way I am?

(3) Yes it does process a fair amount of data (on the same server).
However it shouldn't take more than an hour. When it does hang, it will hang forever until kill it.
We saw this before when it was an 'OS Command' as well as now when it is a T-SQL command.
Hence why I was working on (2) to be a job monitor to help troubleshoot it if/when it next happens as it's intermittent. :)

(4) Originally had separate jobs with a job step that was an OS Type command:
* full process historical (data) cubes @ 12am
- as cubes unavail during full proc
* full process recent (data) cubes @ 12am
- as cubes unavail during full proc
* refresh historical (data) cubes @ 6:30am/pm
* refresh recent (data) cubes @ 6:30am/pm
* fill SQL database (cubes source data) with new data @ every 10mins
- but not around 12am or 6:30am/pm to avoid processing at same time as cubes

Now the problem we were having was that the jobs would overlap & had regularly data issues.
So looking for a solution to this. The idea was to put them into one job and only run a job step if it is scheduled to (via a Scheduler table in SQL database)
hence the 'Scheduler' table in the SQL database & the need for T-SQL code to check/update Scheduler table before/after command.

Yes this is successfull, however when want to kill job, it wont kill if an xp_cmdshell command is running - until it completes, however when it was an OS type command it would kill very quickly.
Dont want to have to restart SQL Server to kill the job because of the xp_cmdshell.

I'd love an alternative to xp_cmdshell... was trying to think of the best way to do it as explained in my original post. :)

Already have 'Scheduler' jobs that simply turn on a flag in the Scheduler table & have the 'Main Job'.

Want to be able to store the the success/failure (errcode) result of each job step in the Scheduler table & turn the Scheduler table value off if it succeeded.
Then want to be able to return the overall success/failure of the job.
Ie... if a job step is not scheduled to run, dont want it to return a failure as it didnt actually fail.

Eg:


-- 
-- ============================================
-- JOB STEP 5: FULL Process 24hr Cubes IF required  (1am)
-- ============================================
 
declare @result int
declare @processcubes int
 
set @processcubes = ( select [value] from Scheduler where [key] = 'FULL Process 24hr Cubes' )

if @processcubes = 1
begin
  exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
  update Scheduler set [errcode] = @result where [key] = 'FULL Process 24hr Cubes'

  if (@result = 0)
  begin
     print 'Success'
     -- only turn flag off IF SUCCEEDED
     update [Scheduler] set [value] = 0 where [key] = 'FULL Process 24hr Cubes'
  end
  else
  begin
     print 'Failure: error code=' + CONVERT( char(8), @result )
     RAISERROR('24hr Cubes failed to FULL process.', 11, 1)
  end
end
else  
  print '24hr Cubes not scheduled to be FULL processed.'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-23 : 22:58:29

You edited a post 15 months later? Still have questions?

shell_l_d
Starting Member

17 Posts

Posted - 2011-03-24 : 04:52:05

I very slightly edited 1 of the SQL queries above, I didn't think anyone would be advised as though it was a new post.

However it is still an outstanding issue, as seen by my last post above.

SQL Server 2000 (& 2005)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources