On Windows 2000 the service deals with userenv/1000 where the message indicates that the profile is not unloading and the error is "Access is denied". On Windows XP and Windows 2003 the equivalent events are userenv/1517 and userenv/1524.
To accomplish this the service monitors for logged off users that still have hives loaded. When that happens the service determines which applications have handles opened to the hives and releases them. It logs the application name and what registry keys where left open. After this the system finishes unloading the profile.
Download: User Profile Hive Cleanup Service