Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Issue with SQL Server 2005 SP2


By:   |   Read Comments (8)   |   Related Tips: More > Upgrades and Migrations


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem
If you haven't heard already, there was a problem identified with service pack 2 for SQL Server 2005.  The problem has to do with the "History Cleanup" tasks and the "Maintenance Cleanup" tasks. The issue is that the interval that these tasks runs is not based on what you think when you set this interval.  Based on this, your backup files or other files that are cleaned up by these tasks may be prematurely deleted or these files may stay around longer then intended and therefore possibly cause disk space issues.

Solution
If you haven't yet installed service pack 2 for SQL Server 2005 then you won't be impacted.  The version that has this issue is 9.00.3042.00.

You can see what version you are running by using SQL Server Management Studio.  The version number is displayed next to the instance name.  You can also run SELECT @@version to get the version number.  Refer to this previous tip to see a list of all versions.

If you have version 9.00.3042.00 installed you should download and apply the general distribution release update.  This can be found here Critical Update for SQL Server 2005 Service Pack 2

After the update is applied you should be at version 9.00.3043.00.

The Microsoft KB article also states that you might have to restart the computer after the update.

Also, if you created or edited maintenance plans or Integration Services packages by using the initial release version of SQL Server 2005 SP2, you must verify and update the cleanup task intervals after you apply the update.

To do this, follow these steps:

  1. Open the maintenance plan or the Integration Services package
  2. Open each cleanup task
  3. Adjust the cleanup interval to the correct value
  4. Save the plan or the package

One last note, this does not affect SQL Server 2005 Express Edition.

Next Steps

  • Check to see what version you are running
  • Download and apply the update
  • Check your maintenance jobs and SSIS packages to correct the problem
  • Verify that you are not missing critical files that these jobs create, if you are try to recreate the files if necessary
  • Your SQL Server 2005 Express instances are not impacted and therefore nothing needs to be done


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, December 06, 2014 - 12:02:45 AM - Dinesh DBA Back To Top

I had sql 2005 express edition with service pack 1 which 32bit and windows 2008 standard edition. I tried service pack 3, 4 aslo with both edition 32 and 64 but .it is not working..Please share email so i can share summary.txt


Friday, December 05, 2014 - 9:05:58 AM - Greg Robidoux Back To Top

Hi Dinesh,

what version of SQL do you have installed right now?  What was the last service pack you installed?


Friday, December 05, 2014 - 5:54:40 AM - Dinesh DBA Back To Top

Hi Team,

My sql server is 32 bit mssql 2005 express edition and windows 64 bit.I had tried 

http://www.microsoft.com/en-us/download/confirmation.aspx?id=7218

SQLServer2005-KB2716429-x86-ENU tried also showing error..

The product instance does not have prerequisite update 5000 installed. Update 5069 is dependent on prerequisite update 5000. Exit setup and refer to the Knowledge Base article to find the prerequisite patch. Install the prerequisite and rerun ..

 

Any solution...


Friday, December 05, 2014 - 3:04:47 AM - Dinesh DBA Back To Top

HI Team,

I have started setup but feature selection window status =Not valid.

 

Please guide on same.


Thursday, March 08, 2012 - 5:37:31 AM - Satish Back To Top

9.00.3042.00 in this version it works after updating above metioned patch but in case of below

 

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)   May 26 2009 14:24:20   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

 

what is the solution

Satish


Thursday, March 08, 2012 - 5:35:08 AM - Satish Back To Top

I have same issue on

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)   May 26 2009 14:24:20   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

With SP3

 

Please help me on this.

Thanks,

Satish


Friday, January 15, 2010 - 3:44:36 PM - admin Back To Top

Try to update the client tools too.

You can apply SP2 to your workstation to update the client tools and books online.


Friday, January 15, 2010 - 4:06:49 AM - yakaaithiri Back To Top

hi i am facing some error when opening the maintainence plan in sql 2005.

When i say Select @@ version

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)   May 26 2009 14:24:20   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

 the following result comes.

When i click on the new maintainence plan wizard, then the following error pops.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Unable to execute requested command.

------------------------------

Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'. (Microsoft.SqlServer.Management.MaintenancePlanWizard)

------------------------------
BUTTONS:

OK
------------------------------

please help me resolving this .

 


Learn more about SQL Server tools