I'd create the new partitions well ahead of needing them- there isn't a big overhead in having (a few) superfluous ones. Your system should try to create at least the next couple of partitions, and should be run more than once a day. Moreover, you should probably have something monitoring its success.

We use something similar (albeit with tables rather than partitions) which runs every 10 minutes trying to create tomorrow's table. This gives it about 144 chances to succeed before we need it.

Likewise, the process which drops old partitions should drop ALL of the obsolete ones, not just one, that way if it's skipped it can catch up.

Is there a reliable way of trapping errors and writing to logs from a stored procedure (i.e. event) ?

Mark