Here's some more info... 🙂
--------------------------
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 = '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 = '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 = 'FULL Process 24hr Cubes' )
if @processcubes = 1
begin
exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
update Scheduler set [errcode] = @result where = 'FULL Process 24hr Cubes'
if (@result = 0)
begin
print 'Success'
-- only turn flag off IF SUCCEEDED
update [Scheduler] set [value] = 0 where = '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 = 'Refresh Cubes' )
if @refreshcubes = 1
begin
exec @result = master.dbo.xp_cmdshell 'DTSRun /~Z0x..................................................'
update Scheduler set [errcode] = @result where = 'Refresh Cubes'
if (@result = 0)
begin
print 'Success'
-- only turn flag off IF SUCCEEDED
update [Scheduler] set [value] = 0 where = '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 = '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 = 'FULL Process Cubes'
if (@result = 0)
begin
print 'Success'
-- only turn flag off IF SUCCEEDED
update thedb..Scheduler set [value] = 0 where = '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 = '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 = '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 = 'Reindex Tables'
-- set errcode to highest failure error code returned
update Scheduler set [errcode] = @result where = '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
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