I think Prolog rules can replace a lot of spaghetti code: stuff like "Usually do this, but then other times do this, unless this other thing is true. If there's a parse error, then do xyz and keep going." In ordinary procedural code, the exception cases can dominate the body of a function and may need to be deeply nested, but in Prolog, it's possible to cordon them off or express them at the top level.
I suspect it might be most useful as a DSL you can embed inside of other languages, though I haven't seen a good implementation of that. (The clojure core.logic library mentioned elsewhere looks really promising.)
I think it may also be useful for domain experts to specify simple rules that capture an aspect of their expertise. If you restrict the domain down enough, and work to figure out what predicates are needed, prolog rules can become easy enough to read that you don't need to know much programming to use them. But the devil seems to be in the details of connecting the core logic into a bigger system that actually does something.
For example, the answer for hooking prolog up to a SQL database is "Look at the ODBC library!", which is not a very satisfactory answer. I think you could build a really beautiful ORM system in Prolog, but nobody's done it yet. (Maybe ORM isn't the right phrase, but a clean way of relating prolog queries to database queries and vice versa.)
The syntax is wonderfully simple, but also has almost no sugar for common idioms. The predicate notation makes it somewhat harder to batch up a bunch of attributes into one conceptual "thing" than in other languages. That means you need to keep positional arguments in your head a lot more. I think aping the class constructor and attribute notation of other languages would be helpful like `my_foo(foo, bar:attr, baz:attr) :- [...].` Then you could reference that either as a constructor (`my_foo(a,b,c)` would state that `a.bar=b` and `a.baz=c`) or to generate goals, like `my_foo(foo), other_predicate(foo.bar)`. Prolog is so old that choices like "make '.' the end of a statement" made sense at the time, but haven't stood the test of history.
(Disclaimer: Many of my criticisms may have perfectly valid solutions in prolog. I'm still learning it.)