Try cutting your batch size down. Make sure you process records in the execute method.
------- EDIT -------
global void execute(Database.BatchableContext BC, List<sObject> scope){
// process records here
// only query records that pertain to the scope
}
global void finish(Database.BatchableContext BC){
// the finish method is to do things once all the batches are complete
// for example, send an email describing how the batch process went
}
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
The Database.executeBatch method takes two parameters: An instance of
a class that implements the Database.Batchable interface. The
Database.executeBatch method takes an optional parameter scope. This
parameter specifies the number of records that should be passed into
the execute method. Use this parameter when you have many operations
for each record being passed in and are running into governor limits.
By limiting the number of records, you are thereby limiting the
operations per transaction. This value must be greater than zero. If
the start method returns a QueryLocator, the optional scope parameter
of Database.executeBatch can have a maximum value of 2,000. If set to
a higher value, Salesforce chunks the records returned by the
QueryLocator into smaller batches of up to 2,000 records. If the start
method returns an iterable, the scope parameter value has no upper
limit; however, if you use a very high number, you may run into other
limits.
One has to be very careful when using a SOQL query in a loop to make sure you don't run into the TOO MANY SOQL QUERIES error -- you need a guarantee that the outer loop has a sufficiently small number of records to iterate over. And using some clever scheme with maps can get the SOQL query out of the loop. But you're not running into this problem (yet). The issue is the current batch size (default = 200) requires you to query too many records. Two solutions:
- Lower the batch size (to 100 or 50) until it works. But try to have an estimate of how many records you'll query given a certain batch size so you know you won't run into errors at runtime (now and in the future).
- You may be querying non-related records. Try refining your queries to only query records that are necessary.
-------- EDIT #2 ------
Here's an example of a method I use to send a nice email describing a batch process to be used in its finish
method
public class util{
public static Messaging.SingleEmailMessage finishEmail(Database.BatchableContext BC){
messaging.singleEmailMessage mail = new messaging.singleEmailMessage();
AsyncApexJob a = [SELECT Id,
Status,
NumberOfErrors,
JobItemsProcessed,
TotalJobItems,
CompletedDate,
ExtendedStatus,
ApexClass.name,
CreatedBy.Email,
CreatedBy.Name
FROM AsyncApexJob
WHERE Id =:BC.getJobId()];
mail.setToAddresses(new string[]{a.CreatedBy.Email});
mail.setReplyTo('yourEmail@someDomain.com');
mail.setSubject('Batch Code Complete -- '+a.ApexClass.name);
mail.setUseSignature(false);
string td1='"border:1px solid green; width=200px;"';
string td2='"width=200px; border:1px solid green; background-color:red; color:white; font-weight:bold;"';
string tdHead='"border:1px solid green; width=200px; color:white; background-color:green; font-weight:bold;"';
string ExtendedStatus='';
if(a.ExtendedStatus!=null)
ExtendedStatus=a.ExtendedStatus;
string tdErrors=td1;
if(a.NumberOfErrors>0)
tdErrors=td2;
string htmlBody = '<div style="border:2px solid green; border-radius:15px;"><p>Hi,</p><p><span style="color:brown; font-weight:bolder;">Salesforce</span> completed running <b>Apex Batch Code<>/b>.</p>'
+'<p>Results:</p>'
+'<center><table style="border:3px solid green; border-collapse:collapse;">'
+'<tr><td style='+tdHead+'>Class Name</td><td style='+tdHead+'>'+a.ApexClass.name+'</td></tr>'
+'<tr><td style='+td1+'>Completed Date</td><td style='+td1+'>'+a.CompletedDate+'</td></tr>'
+'<tr><td style='+td1+'>Status</td><td style='+td1+'>'+a.Status+'</td></tr>'
+'<tr><td style='+td1+'>Job Items Processed</td><td style='+td1+'>'+a.JobItemsProcessed+' / '+a.TotalJobItems+'</td></tr>'
+'<tr><td style='+td1+'>NumberOfErrors</td><td style='+tdErrors+'>'+a.NumberOfErrors+'</td></tr>'
+'<tr><td style='+td1+'>Extended Status</td><td style='+td1+'>'+ExtendedStatus+'</td></tr>'
+'<tr><td style='+tdHead+'>Created By</td><td style='+tdHead+'>'+a.CreatedBy.Name+' ('+a.CreatedBy.Email+')</td></tr>'
+'</table></center>'
+'<p>Enjoy the <b>results</b>!</p><p><span style="font-family:"Courier New", Courier, monospace; color:green; font-weight:bold; font-size:larger;">Scott</span></p></div>';
mail.setHtmlBody(htmlBody);
return mail;
}//END finishEmail
}//END class
Test code:
@isTest
public class Util_test{
@isTest private static void testFinishEmail_Batch(){
list<Account> accts=new list<Account>();
for(integer i=0;i<(math.round(math.random()*200)+1);i++)
accts.add(new Account(Name='test'+string.valueof(i)));
insert accts;
test.startTest();
id batchInstance=database.executeBatch(new test_Batch());
test.stopTest();
}//END testFinishEmail_Batch
}
With test_Batch_code:
global class test_Batch implements database.Batchable<sObject>{
global final string query;
global test_Batch(){
query='SELECT id FROM Account';
}//END test
global database.QueryLocator start(database.BatchableContext BC){
return database.getQueryLocator(query);
}//END start
global void execute(database.BatchableContext BC,list<sObject> scope){
}//END execute
global void finish(database.BatchableContext BC){
Util.finishEmail(BC); // <---- ** The finishEmail method **
}//END finish
}//END test_Batch
You're running into this on production, because you have much more data there than in your sandbox. This is a limit which is a bit harder to test against and discover in advance. You'd do well to integrate it into your salesforce developer conscious.
total number of records retrieved by SOQL queries = 50,000
You will need to use Batch Apex, in which the 50k limit counts per batch execution
These limits count for each Apex transaction. For Batch Apex, these
limits are reset for each execution of a batch of records in the
execute method.
This is clearly stated in the limit documentation. Salesforce enforces these rules to "force" us to use scaleable and resource friendly implementation patterns.
Best Answer
The Limit in your query does not apply on the Total number of rows which were accessed but on the number of AggregatedRows returned. Thus if 1 of your Aggregated Rows has touched more than 1 rows you hit the limit...
This is implied in the documentation: Working with SOQL Aggregate Functions
but it's not 100% clear ;-)