SQL Server Performance Forum – Threads Archive

DTS Jobs Fail.

I am pretty sure that I am going to have to call MS Support on this issue, so I thought I would bounce it off of ya’ll first and get my thoughts in order. Env – Win2k3 SP2 EE, 2 node, 2 instance cluster, MSSQL 2000 EE SP4 Problem. On one instance we have about 180 jobs that run nightly to populate a data warehouse type solution. These jobs basically truncate the tables then pull in data out of CSV files to repopulate the data. Of these 180 jobs, there are approximately 15 that error almost every night with the following error. Error = -2147467259 (80004005) Error string: Error creating file mapping view: Insufficient system resources exist to complete the requested service. Error source: Microsoft Data Transformation Services Flat File Rowset Provider However, they don’t ALWAYS error only about 5 days out of 7. Also, they are not all "big" tables, some load when running in less than 15 seconds, while some run for 7 minutes. Now for the strange part, at least in my mind… These jobs only error when executed via a job, I can, and do, fire up 4 instances of EM and execute the DTS packages manually in parallel and they run just fine. The problem is that I have to do this sometime in the middle of the night before other dependant jobs start up that will fail if these tables are empty. Anyone have any ideas or need any more information?

How many jobs are scheduled at a time?
When you get this error can you identify which job or package has caused this?
Enable DTS package logging if you are controlling the ETL with DTS. Also to determine if server is experiencing this problem, start Windows Task Manager, and then click the Performance tab. At the lower right, locate the Kernel Memory (K) area, and then note the value for Paged. The rootcause of this issue due to many files are open than the memory cache manager can handle. As a result, the cache manager has exhausted the available paged pool memory. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Shortage of memory when all 180 run together perhaps? Could you stagger them? http://www.tradeco.co.uk

They are staggered where they only run one job at a time… Easily identified as the job to dts package ratio is 1:1 That took a lot of time… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> At most, if one ran longer than average, there might be two jobs running at one time. Not a lot really considering we are running up to 5GB of RAM, per instance, (they are always on seperate nodes, each node has a total of 12GB physical, 17GB page file, 3GB switch and PAE are enabled) I have always thought that it was most likely a memory problem, based on the error, the only problem with that is jobs that run after, before, and all around these jobs run just fine with no problems ever. Also, I NEVER have one fail when executing the DTS manually, as noted above, running 4 of these failed jobs in parallel, only through the job.

So having PERFMON (SYSMON) trace at the same time will get you more analysis to assess.
Do you have multiserver administration jobs associated in these instances? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

nope… no multiserver administration. I am watching my paging now, although this is not when the problems are occuring, 8-5 is the prime time for use of the systems, and right now my paging for the last 15 minutes is 0/sec on both nodes. I will set up a PERFMON to run over the night to see what I see. Any suggestions on specific monitors that I need to watch closely?

http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.asp fyi on granular basis anlaysis.
http://sqlserver-qa.net/blogs/perft…t-memory-counters-what-you-need-to-check.aspx on the paging aspect. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thanks!

]]>