Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Question Unanswered: DTS job hangs intermittently (DTSRUN) ???

    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)

    4 DTS packages:
    * 2 x REFRESH cubes:
    - schedule runs twice a day (eg: 6:30am/pm)
    - 2 SSAS tasks: refresh 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):
    Code:
    ----------------------------------------
    -- 
    -- ============================================
    -- 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.'
    ----------------------------------------
    Last edited by shell_l_d; 12-02-09 at 02:02.

  2. #2
    Join Date
    Dec 2009
    Posts
    23

    Question Find SPID for a DTS so can kill it?

    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:
    Code:
    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?

    Code:
    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

    Code:
    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
    RUNHRS 130.295 -- this cant be right???
    program_name Microsoft SQL Server Analysis Services
    login_time 2009-12-02 13:29:24.703
    cmd SELECT
    sql_handle 0x01000500FB024F33C020E8640000000000000000
    Last edited by shell_l_d; 12-02-09 at 01:58.

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    BUMP..... Anyone know please?
    Further info ... my DTS processes cubes of which some have fact tables based on a view (not a table).
    Last edited by shell_l_d; 12-05-09 at 22:32.

  4. #4
    Join Date
    Dec 2009
    Posts
    23
    BUMP..... Anyone know please?

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    BUMP.... as no replies 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).

  6. #6
    Join Date
    Dec 2009
    Posts
    23
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •