SQL Server 2005 Integration service upgrade fails from RTM to SP3 however Database service succeeds
It’s a very rare scenario witnessed when an service pack upgrade from RTM to SP3 fails for SSIS Service due to insufficient memory issue. After lots of analysis and findings and understanding the memory consumption by different component on the box below is what helps to resolve the problem.
Problem Information.
===================
SQL server integration service upgrade from the RTM build to SP3 is failing with the below error message on an existing SQL server instance which was on SP3 :
Machine : xxxxx
Product : Microsoft SQL Server 2005 Integration Services (64-bit)
Product Version : 9.00.1399.06
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0007_UKGC08_DTS.log
Last Action : InstallFinalize
Error String : Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2147024882
Error message: Not enough storage is available to complete this operation.
Error description: Insufficient memory to continue the execution of the program.
Error Number : 29549
The above error clearly describes that there was insufficient memory to continue the operation, so focused towards memory availability and system resource issue while running the SP3.
On this particular environment I observed that it’s a X64 bit box with 64 GB RAM available. Sounds little strange but we were indeed getting this problem. There was no memory settings done neither Lock Pages in memory privilege assigned.
Normally in 64 bit server, SQL Server would consume huge amount of memory specially if max server memory (MB) setting is not configured. So progressed in implementing couple of troubleshooting steps in resolving the issue.
RESOLUTION
===========
1. Configured appropriate MIN and MAX memory settings for SQL Server.
2. Check how much memory is being used by “ dllhost.exe”. Make sure that it’s not taking too much memory on the server.
3. Check how much memory is being used by COM+ objects.
4. Recycle COM+ services.
5. Make sure that MSDTC is running.
6. If, its running then try to restart it.
7. Unregister the DLL via regsvcs /u msmqtask.dll
(To un-register: "C:\Windows\Microsoft.NET\Framework\v2....\RegSvcs.exe" /u msmqtask.dll
To register the DLL: "C:\Windows\Microsoft.NET\Framework\v2....\RegSvcs.exe" msmqtask.dll /tlb:msmqtask.tlb )
8. Run the setup again and try installing SSIS again and this time it succeeded.
9. Re-boot the server.
While working on the above, it will be helpful if we can always keep an MS DTC or MSMQ skilled engineer informed.
Additional Information:
====================
While analyzing this issue had come across similar issue for which the solution was almost the same to delete the MSMQ Task component.
Gaurav Srivastava
EMEA-SQL Server support engineer
Reviewed By
Nickson Dicson
Tech Lead – EMEA DSD