Saving server resources while executing a job

[mtawk]

I have a job which is taking all the resources of the SQL server and slowing it down for the users.

Is there a way I could limit the server resources given for the execution of this job ?

 

[pgtek]

hi

do you have an other server where you can run your job?
we have a developement server and a production server

pgtek

 

[SQLSister]

Several things to do

First schedule this job for off hours if possible.

Second, what is the job, maybe you are not doing it very efficiently and the time it takes to run can be substantially improved.

 

[mtawk]

The job is on the production server.

The job is actually a process of transferring a big amount of data from SQL server to AS400 DB server via linked server.

This process is taking all the SQL server resources, I want it to work with lower resources on a longer period of time.

 

[SQLSister]

You could consider doing the data transfer in batches, that should prevent the server from staying too tied up.

see the following FAQ for information on batches:

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
faq183-3141