Personal preference, really. I tend to be a power-user, so don't want to get asked about any action which I take frequently.
"Continue"
"Are you sure?"
"Yes I'm sure!!!"
I've suffered from repetitive stress injuries in both wrists for the last 10+ years; hazard of my profession and hobby - so the fewer mouse-clicks my favorite applications require, the more I like them.

If the answer to "Are you sure?" is going to be "Yes" 99% of the time or more, I don't want it to ask me.
That said, I
do want a confirmation on infrequent, irrevocable actions with the potential for severe negative user-impact:
- "Are you sure you want to delete your save game?"
- "Are you sure you want to release your star striker on a free?"
- "Are you sure you want to give the board an ultimatum?"
So, I'd be quite happy with a compromise like:
Are you sure you don't want to give a team-talk?
if you accidentally continue without setting one.