Being on-call I received an alert for a client with the following message – ‘The following jobs(s) failed to execute on server ComputerName\InstanceName : MaintenanceName.Check DB Integrity.’ This is a standard Maintenance Plan performing a Database Integrity Check.
The Environment setup is 32 Bit SQL Server 2008 Enterprise Edition running on Windows Server 2008 Enterprise Edition with 7.85 GB of RAM.
My first point of call was to look at the Job history in the SQL Agent. This did not tell me very much other than the package execution returned DTSER_Failure (1) as can be seen from the Job History screen shot below.
With this not giving a lot of information my next point of call was to look at SQL Server Error log to see if anything funny was happening. This also did not reveal a lot of information. The only message which stood out at the time of the job running was
This message is linked to the running of the sp_configure command. Still with not a lot of information to go with I had a look at the Event Viewer. Just like my first to places to look this also did not give a lot of information.
My next point of call was to have a look at the Maintenance Plan history. This is when I started to get some more information to help with the job failure. This showed the error message ‘Alter Failed for Server ComputerName\InstanceName’ This ties back to the error message we found earlier in SQL Server Error log ‘Configuration Option ‘user options’ changed from 0 to 0’. So is this a permissions issue that the account running the job does not have the appropriate level of rights to change the configuration options? After checking the account that it is running as (SQL Agent Service Account) I discounted this as an issue.
I next had a look at the server level configurations to see if anything stands out. sp_configure showing advanced options gave me what I was looking for.
The AWE Enabled option was turned on. Now on its own this should not be any cause for concern, however if you are enabling this option then you also need to enable the Lock Pages in Memory policy. If you are going to use these 2 configuration options it is also recommended that you set minimum and maximum available memory options for the instances of SQL Server. After checking the policy and discovering that it was not turned on I then disabled the AWE Enabled option.
After changing this setting the Maintenance plan completed successfully. Why or who changed the setting I am not sure and I need to check with the client. It may be this was changed for a specific reason and that the Lock Pages in Memory will also have to be set. Will have to discuss and do some testing with the client.
Leave a Reply