Boyan,
Thanks for providing the video. It handles 90% of all error cases, the problem I was facing was more complicated. The standard setup outlined in the video, and countless articles, works well enough in the common case that a single page has something has something wrong, but the site as a whole is still functioning.
The case I am dealing with is when the entire Sitefinity application has broken down. This is easy enough to replicate if Sitefinity is on a separate machine from its database. Just sever the connection to the database, and the entire app falls apart. As a test, I placed a simple .jpg in the root directory. When the database was down, I could not even browse the .jpg. Any call to the site resulted in a "yellow screen of death". It appears that Sitefinity routes all requests, even static content like images and html files.
This is tragic in the case of the custom error page, which is under the site directory. In the moment that it is needed most, it cannot be served. I had already taken the step of creating a static custom error page as html, so that it would not rely on Sitefinity for rendering, as my dynamic 404 page does. However, there were still a few more steps required.
I had to set the responseMode="File" instead of "ExecuteURL". This causes IIS to serve the file directly instead of trying to execute the url, which is important in the case of a server error, because the url may not be able to execute. Next, I had to set existingResponse="Replace". The default behavior is for IIS to serve the custom error, and then pass on the response code (500 in this case). I had to set replace so that it would serve the error page with a 200 message. Why? Because otherwise the site's customErrors section would catch it!
Of course, when the customErrors section caught the error, it would try to serve the page through the application, and I would again receive a yellow screen. Replacing the response code prevents the second error handling from being hit at all.