Head over to C:\windows and open the file name svcpack.log.
After a windows update failure you will see in this log file the reason for the failure. For every failed SP3 that I've experience it was the result of RDP 6.0 MUI PACK (Update for Windows XP (KB925877).
However, every time I checked Add/Remove Programs - it was never in the list. In order to 'remove' this update, you need to manually remove it from the registry. Open the registry (Start > Run > regedit), navigate to and delete the following entry:
Reboot and try installing SP3 via Windows Update again. (Note: you might not actually have to reboot).