Problem Description in our scenario:
We have a default instance of SQL Server 2000 Service pack 4. When we try to run a job, the job doesn’t run. We don’t see any failure messages either. When we click the job properties and then click on “Job History” it gives a message stating "No History information"
In the Enterprise Manager, when we click on Jobs we see the Status Column as “Not Running”; Last run Status as “Unknown”; Next Run Date as “Date and time not available” .At the same time, few other jobs on the same instance are running successfully.
Error Message :
Nothing specific. We just see that the job doesn’t run.
In the Enterprise Manager, when we click on Jobs we see the Status Column as “Not Running”; Last run Status as “Unknown”; Next Run Date as “Date and time not available” .
SQL Server 2000 Standard Edition SP4(8.00.2039)
Windows 2003 Standard R2, sp2,x64 bit 3790
No Cluster configuration.
No Replication Configuration.
Note: 64 bit box with SQL Server 2000 32 bit.
Cause of the issue in our scenario:
We checked the version of XPSTAR.DLL from the following location: [C:\WINDOWS\SYSTEM32\XPSTAR.DLL]
File Description: Extended Stored Procedure DLL for SQL Enterprise Manager
Product Version: (8.0:1.94)
File Version: (2000.80:194.0)
The “ XPSTAR.DLL” was still on RTM(Release to Manufacture) Version of SQL server 2000, though Service Pack 4 was applied.
Workaround in our scenario:
SO AT THIS POINT, THE ONLY FILE WHICH WE ARE CONCERNED IS :- C:\PROGRAM FILES (X86)\MICROSOFT SQL SERVER\MSSQL\BINN\XPSTAR.DLL
We did a replace of this file as shown below:
1. We stopped the SQLServer instance.
3. We extracted SQL server 2000 service pack 4 and replaced the XPStar.DLL file from the following location “C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Binn” with service pack 4 version of XPStar.DLL.
4. Started the SQLServer and SQLServer agent.
We were then able to start the job and the job ran fine and the history tables were updated.
Note: We can also reapply the service pack 4 to resolve this.
Note: Use Checksym.exe to find the version of all the SQLServer DLL’s and EXE’s . If there are more than 1 DLL or EXE’s which are below sp4 then we recommend you reapplying the service Pack.
CheckSym.exe -P SQLProcessID -R -O C:\SQLServr.txt CheckSym.exe -P SQLProcessID -R -O C:\SQLagent.txt
Manikandan Support Engineer, Microsoft SQL Server
Reviewed By Karthick Krishnamurthy Technical Lead, Microsoft SQL Server
Having similar issue in my environment but how to ensure xpstar.dll file has correpted.
because such issue happening in weekly basis or sometime monthly basis but the issue resolve if i restart the server. Iam not sure this solution will help me.
so can you help me on how to re-produce such error or validating the correpted file.