DTSRUN.EXE PROBLEM
-
We have a number of sql server which run dts packakges. These packages have been created over 2 years ago and have been running fine everyday without any problems. Recently we noticed 1 server having problems running the dts package. I noticed a regualr occurance every Monday evening.
Now we seem to have this problem on 5 other server too
All security setting for MSSQL services are setup correctly and run under a domain account. This account hasn't been changed recent and has access to run DTSrun.exe.
We use a sql agent job to execute the dts run command with the dts package guid like -DTSRun /~Z0x1F17B927F644E77F363F5F825BB65A51685F4C3C4F66A94097764453031AD9681F9E043E163B6BAF2A65B4F94A3114D3A82CCA7A9E666416575E5CA933DCDFD72B4959A7D784C65D5971FCD07DBB0
The Job runs at the scheduled time and states its executing. In taskmgr i see a dtsrun.exe. After this nothign happens. No Tasks within the dts are run. No error log in even't viewer no message is returned from the job or the logs from the dts packakge are populated.
i can run the dts package through the command prompt and though EM.
I hav also change the services to start up as local system account, I have change the owner of the JOb but still no luck.
through QA I can run xp_cmdshell 'ipconfig' ftp dir etc but when i run dtsrun.exe it just hangs again with no error. I check taskman and dtsrun.exe is running in the processes. After rebooting if i run the command again through QA it returns the help infomation for dtsrun.
I have renamed dtsrun.exe moved it to a different location onthe server and specified the path in the sql job and through QA.
The only way to resolve this is to reboot the server. Then after a week the same problem occurs again.
To eliminate Virus chekers have have disabled all
I am running win2k advance and server
sql2000
sp3a
Anyhelp on this problem would be great and if you require furhter infomation please let me know.
Thanks
Bhav
-
The fact that the problem goes away with a reboot and comes back in a week smells like a memory leak to me. The release notes for SP 4 lists five or six fixes for memory leak problems. I would review the release notes looking for a match or just make the jump to SP 4.
-
What is the DTS Package doing? What is the first task?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Try running profiler when the DTS package starts and see if you can capture any events which are occuring.
-
the dts is runnind xp_SMTPSendMail80 then a simple EXEC time_delay '000:30:00'.
On other packages it call a inhouse written exe which bcp's datainto oneof the db then preform a number of tasks after (processing data cubes).
I'll try and look at profiler to see if i can get any futher infomation but i don't think i will as i have enabled job logging for each step.
edit job / edit step / advance tab / output file
another thing is when the problem exists on the server if i run xp_cmdshell 'dtsrun' through QA it just hangs exactly like when run from a job. After reboot i get the help associated with dts run
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):
Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also specified)
/~R Repository Database Name <uses default if blank; loads package from repo
sitory database>
-
In your first post you say it resolves its self and runs for a week after a reboot. Can you explain this a little more and describe each step of the package?
After you start the package does sp_who2 show you if it is running at all and\or what step it is on?
David
-
I suspect that it is a problem with xp_SMTPSendMail80. I think it is sitting there trying to send the email, and it never actually fails, just keeps trying. SQLMail has the same problem in SQL 2000 pre-SP3. Rebooting was the only way to fix it. I ended up changing a large number of DTS packages so that there was no precedence between the initial email notification and the following steps. At least that way if the email notification failed, the "real work" was still completed.
Of course your email task may be more than just a notification, and you might have to have the precedence in there. But I think it is definitely worth taking a look at that procedure to verify that it is the problem and considering alternate solutions.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
ok
The dts packages are scheduled to run via a sql job. this job runs at 19.30. the job status at this time states that its executing, but none of the steps in the dts package run. Alos no dts log file is produced either.
Dtsrun appearc as a windows process and dose nothing else.
sp_who2 dosent show me anything and sql profiler only logs the command xp_cmdshell 'dtsrun' when i kill the process through windows taskmanager
The dts package itselft runs a number of steps.
1) bcp data into Live db
2) execute sp's to move data into retention tables
3) process analysis server cubes
4) extract csv file from cube
5) call a batch file to ftp csv file
6) build excel reports from analysis cube
7) email excel files
8) email logs to admin's
-
Have you tried placing debug lines in to identify which steps are executing?
E.g. write an event to the event log, a log file or better still create a table and add rows to that like:
bcp starting at xxxxxx
bcp ending at xxxxxx
sp x completed
etc.
This can help you see where you are hitting problems
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply