I noticed the database retention policy hadn't been set on our vc and it's been running for about a year. If I turn on the retention policy now will it actually do the cleanup of the old entries the next time the prune runs? Or I need to run the cleanup script first to actually get the data pruned?
The old entries will get cleaned up the next time the stored procedure runs on the VC database regardless if this is sql express or full version. I'm not sure what the scheduling is, but you can manually execute the proc if you want to see it take effect. I'm running vCenter Server 4.1 so maybe this doesn't apply to older versions... If you connect to the vCenter database with management studio you'll get a list of stored procs by expanding the vc database and going to programmability and locating the stored proc called "dbo.cleanup_events_tasks_proc." Execute that and you should see some results.
To cleanup performance data I believe Vmware has some KB's on the scripts to run.
Hope that helps,