Yeah, I've seen SINGLE_USER fail too. There is a lag, however tiny, between that GO statement and the next statement in the batch. It only takes one connection to muck you up. We usually use RESTRICTED_USER because only DBA's have it, so we're the only ones that we have to worry about.

According to BOL, setting the database to OFFLINE is "clean" which usually means that transactions are completed or rolled back prior to the change in state. I tried it out (not in production) and it worked pretty well, maybe I'll use this one instead from now on:

ALTER DATABASE [AdventureWorks]

SET OFFLINE

WITH ROLLBACK IMMEDIATE

GO

DROP DATABASE [AdventureWorks]

GO