A. A. All service-packs come with problems, however in most cases they fix a lot more problems than they cause. 99% of bugs you may find in SP4 will be present in the gold release and all subsequent service packs - they are un-fixed/unknown bugs that are present in every release.
SP4, as SQL 6.5 service packs go, is a very stable one. For a list of bugs fixed in SP4, download it and check the fixlist.txt file.
There are a couple of known problems with SP4 that don't occur with other service packs.
1. There is a regression of a numeric index bug. With SP4 if you say
WHERE <fieldname> = 1
then it will tablescan even if there is an index on the numeric fieldname. But if you specify
WHERE <fieldname> = 1.0 then it will work ok.
2. The Instdist.sql script that comes with SQL Server 6.5 Service Pack 3 can cause problems with replication if it is run more than once. This file is also distributed with Service Pack 4, so if you go from SP3 to SP4 then it is run a second time. This obviously only affects systems that use replication. Full information can be found in Q184882 in the Microsoft Kb.
