I would definitely like to know the situations where service brokers turn into nightmares.

As far as huge databases are concerned, service broker is hardly influenced by the DB size. All that matters is the volume of data that is being transported. The best thing about service brokers is their asynchronous nature. So they always have a chance to process the requests at some point of time without affecting other processes. On the other hand if you go for the same communications with triggers (or something similar) you will find yourself at the waiting end until the communication is done.

For an example, you can take one central auditing server which maintains records of all DDL operations across all of your other servers. This has been implemented by many organisations and prove much more reliable and better auditing mechanism than triggers.