Just a while ago one of my team members reached out to me to inform that one of the Backup Jobs had failed today. I was sure that he had done all the homework before reaching out to me. Still I did not hold back myself from asking that question to him. He had checked the logs and had found a different error in the Job Step Log.
The SQLDMO 'Application' object failed to initialize (specific error: One of the library files needed to run this application cannot be found).
Even I saw this error message for the first time. Before digging deep into the error message details, I did the following checks.
- Did all the backup/maintenance jobs failed with the same error? – Yes, both the backup job that ran today failed with the same error.
- What is the version of SQL Server? – SQL Server 2008 + SP2 (10.00.4000)
- What does the job step do? – Execute EXECUTE master.dbo.xp_sqlmaint N’-PlanID…………..
The Maintenance Plans in SQL Server 2000 makes use of xp_sqlmaint extended stored procedure to run the maintenance tasks. Yes I could see two maintenance plans under the Legacy maintenance plans in SSMS. But this server is running on SQL Server 2008 and this job ran fine till yesterday. I was little puzzled for a moment.
Oh no. I recollected reading an email that someone had “successfully” upgraded this instance from SQL Server 2000 to SQL Server 2008 an hour ago! But that person forgot that any Production Instance will always have one or more Maintenance Plans/Jobs. Anyways the definition of success varies from person to person. I have at least a couple of such upgrades where something or the other would have been messed up and still it was termed successful. The classic example I remember is an instance running on SQL Server 2005 the compatiability level of master database was 80 and that of the user databases was 70!
Damage is done already. How to fix it? There are two options. As per official documentation, one is to upgrade the legacy Maintenance Plans and the other one is to install backward compatibility SMO/DMO drivers. Out of these two I find the first option to be more feasible.
As seen in the below screen shot, just clicking Upgrade on the maintenance plan would upgrade the Maintenance Plan to SQL Server 2008.
However I am little circumspect whether the upgrade would be absolutely perfect. That is the reason why my team member who has created the Change Control for this upgradation has kept the Implementation window for 6 hours. I did not feel good for this incident since one is cleaning up the mess someone else had created. The good part is that my team learnt something new today and history will not repeat when they do it.