Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended

If you have not installed SQL Server 2005 SP2

The original release version of SQL Server 2005 and SQL Server 2005 SP1 do not have this issue, and the current SQL Server 2005 SP2 download is updated to resolve this issue. If you have not installed the initial release version of SP2, you can download the current release and install it.

Note If you try to apply the SP2 refresh over a previous SP2 installation, no files are updated. Instead, use the general distribution release (GDR) update below.

If you downloaded SQL Server 2005 SP2 before March 05, 2007

If you downloaded SQL Server 2005 SP2 before March 05, 2007, apply the general distribution release (GDR) update for those SQL Server 2005 SP2 installations. The GDR update is available at the Microsoft Download Center:This GDR update is also available through Microsoft Update. This GDR update is applied automatically if you are using automatic updates. However, if you are using failover clustering, you must manually download the GDR update and apply the GDR update. Microsoft Update does not apply the GDR update to failover cluster nodes.

After you apply this GDR update, you might have to restart the computer.

The GDR update corrects the interpretation of cleanup tasks that were created by using the original release version of SQL Server 2005 and by using SQL Server 2005 SP1. 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 GDR update. If you do not do this, cleanup tasks will retain data longer than intended.

To verify and update cleanup task intervals, 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.
   

When you specify a server, the SQL Server 2005 SP2 Maintenance Plan Utility can identify maintenance plans that were edited by using the original release version of SQL Server 2005 SP2. You can use this information to limit the number of plans that must be inspected. To obtain this utility, visit the following Microsoft Web site:

Files that are updated by the GDR update and by the SP2 refresh

Tests to determine which version of SQL Server 2005 SP2 is installed should inspect the file in %ProgramFiles%\Microsoft SQL Server\90\DTS\Tasks. If the SP2 file version is greater than or equal to 9.00.3017.00 and less than 9.00.3042.00, the SP2 version is pre-release and you should install the SP2 refresh. If the file version equals 9.00.3042.00, the version is the initial SP2 release, and you should apply the GDR update.

The GDR update detects file versions and updates the files as necessary. However, if you need to verify file versions, you can use Microsoft Windows Explorer or the Filever command-line utility:

• To check the file version on individual computers, open Windows Explorer, right-click the file, select Properties, and then click the Version tab.
  
  
• To help with checking file versions on many computers, the Filever command-line utility is available. For more information about the Filever command-line utility, click the following article number to view the article in the Microsoft Knowledge Base:
  
  
  913111 How to use the Filever.exe tool to obtain specific information about a file in Windows
  
  

Files that are updated by all SQL Server 2005 hotfixes and GDR updates

Some files are updated by every SQL Server 2005 hotfix and GDR update, even if no changes were made in the source code. For example, if Sqlservr.exe is updated, SELECT @@version shows an updated product version.

The following table lists the files that are updated regardless of code changes.