HI Can any body help me, We are scheduled DTS packages to run overnight. All of a sudden batch time seems to increase by 3 hours. Below are our Server details MS SQL Server Standard Edition Window NT -5.0 4 processors 2GB memory
Is the DTS package processing more data? Are the tables being loaded being used by other users? If so this might cause some locking. Have you changed the structure of the tables involved (added/removed indexes)? Are there other things that have been added to the nightly window that might be using some of the resources or causing locking issues? You might want to start a profiler trace and System Monitor trace to determine what is happening at night.
can you replicate this issue on a test box? If fast on test box with copy of prod databases, then I agree with Greg., but profiler is the way to go to check out why.