Sunday, March 25, 2012

DTS Error problems driving me mad!

I have a DTS scheduled to run daily. It has run OK for the past few months, but now always fails, the following error appears:

dtsrun.exe - application error

the instruction at 'xxxxxxx' referenced memory at 'yyyyyyy'. The memory could not be 'read'. click on ok to terminate the program.

The dts itself runs fine if started manually, but always fails as a scheduled job, even if its started manually.

Help!!!!!Eventhough a serious violation like this shouldn't happen, it may be caused by insufficient permissions. The job is run with the permissions the user has who is configured to run sqlserver (EM, Server properties, Security-tab).|||I have never encountered this particular error myself; however if I may toss in a couple of thoughts:

1. Is there an increased load being placed on the server of late?
2. Are there a number of DTS tasks scheduled to run simultaneously?
3. Are there other OS or application tasks scheduled to run at or near the same time as this package?
4. Do you know precisely what step DTS is failing on?
5. Is it possible to schedule and run this task from another SQL Server?

Some additional thoughts (which you may already know and I apologize if you do):

1. DTS runs in a separate memory space from SQL Server; multiple DTS packages running simultaneously will degrade the amount of RAM available.
2. There is a windows registry setting for throttling the number of DTS (or was it command -- .exe -- objects that can run simultaneously). I think the default is 10. Search MSDN.
3. If you use DTS heavily on one server, consider throttling back the amount of memory available to SQL Server; I know that this is NOT recommended by MS, but I have done this in the past with a good deal of success.

I hope that you can trace the roots of your issue; good luck.

Regards,

hmscott

No comments:

Post a Comment