Based on the numbers I checked on classicdb:

Holy Nova has a 7.14% spell power coefficiency. Costs 675 mana with mental agility. Deals ~200 damage per target.
Enough mana for ~7 Holy Nova at lvl 60 (assuming you are geared already). 7 * 200 * 4 = 5600 damage, before considering crit and spell power.
Looking at some random mobs in BRD it says they have 6-8.2k hp (on classicdb).

Would deal some more damage with Power Infusion and Inner Focus, but both have a 3 min cd.

So I think it would be able to run lower lvl dungeons (although with a lot of downtime drinking) but for proper dungeons it would most likely be very hard and bothersome (might run out of mana before killing mobs, some mobs might run out of aoe and survive so then you might need to wand them down, some mob might run into another group and then you get a new group to fight with 0 mana etc).

Moorea said he used it but in TBC, so that is not the same as classic...
Apatheist said it is not viable in classic.