What is the purpose of having multiple memory card slots and why would you want them?
Professional cameras such as the Canon 1D Mark IV have dual memory card slots for a variety of professional use cases. I will break them down into three main groupings:
Speed, Redundancy, and Buffers
A theme across the three groupings can be realized, where you will find speed, redundancy and the cameras use of the buffer all play a role. It comes down to what is the most important aspect of your photography, and where do you place your priorities.
If you consider speed to be your number one goal(which it may be if you chose a high end body that has the dual memory card slot feature) then you may want to choose the ability to write different formats. This will prevent the camera from doubling the data going through it on every shot as in the case of mirroring the image. If redundancy and near 100% image preservation is your highest priority, you likely want to mirror the image to both memory cards. If you are more casual about your redundancy and or speed is not the highest priority, you may choose the convenience of having the camera switch to the second memory card when the first fills up.
Other points to consider
If your camera only uses compact flash cards, it will not currently allow use of an Eye-Fi adapter for wireless transmission of images. If the dual memory card support adds an SD card slot, this may be an additional advantage.
There was a time when some cameras came with dual memory card slots to help consumers switch between competing formats, such as xD, Memory Stick(Sony), Compact Flash, etc. These early implementations were not designed in the same way as today's professional dual memory card slots, they were simply to ease the transition for consumers between formats.
Some manufacturers have additional features specific to them, such as Nikon's ability to write video to one memory type and photos to another, or Canon's ability to accept both Compact Flash Type I and II cards.
Overall the dual memory card slots add professional level options that further enhance the photographic possibilities. If you are interested in the fastest possible speed, data redundancy or work in poor weather conditions - these features will provide enhanced opportunties. They do add an amount of additional size to the camera bodies, but for most professionals using these bodies, the features are welcomed or even necessary.
It's for redundancy, in case one card corrupts. Never EVER want that to happen in the middle of a wedding and you lose half the day!! Some photographers use it to store JPEGs on if the bride and groom want a slide show in the evening (tacky but some people love it!) while keeping the RAW files on a different card for editing later.