I installed an SP2 upgrade over the weekend on BizTalk 2004. Here were the take aways.
1. Always have the BizTalk user account as God mode for the SQL server that hosts the BizTalk Databases.
2. Shrink the MsgBox and Tracking Databases before installing the SP2 upgrade. (http://msdn2.microsoft.com/en-us/library/bb203857.aspx for the MsgBoxDb and KB894253 for the Tracking DB)
3. If the database file sizes don’t decrease use the following SQL methods:
dbcc shrinkfile (<DatabaseName>, <size in MB>)