Like most of you, I have several "smaller" compact flash cards -- a 2 GB SanDisk Ultra II, 3-each 1 GB SanDisk Ultra II, a 512 MB Ultra II, a couple of no-name 512 MB "Toshiba" compact flash (not anywhere close to being as fast as the SanDisk cards), and a couple of freebie 256 MB compact flash cards from long ago. I use all of them regularly -- even the old 256 MB ones occasionally -- in both my Nikon D-100 and my trusty old Oly C-2500 (the Olympus uses both CF and SmartMedia).
As for going to 4 GB or larger compact flash card: I don't particularly need to jump to 4 GB, though I had one occasion where it would have been nice to have had more than a 2 GB compact flash card in my Nikon, since I suddenly found myself out of picture storage space on my 2 GB compact flash card and fumbling through my camera bag for one of my 1 GB or 512 MB compact flash cards.
I suspect it would be quite nice to have a big ol' 8, 12, or 16 GB compact flash card in my Nikon for a weekend's worth of shooting, but I really don't need that much picture storage space -- unless I decided to start shooting TIFF files. But for some photographers out there, 8 GB of storage (or more) is required.
There are situations that you can run into that require you to keep shooting or to not open the camera to insert fresh storage, such as shooting in dusty or wet conditions, doing underwater photography, or unattended photography using an intervalometer. There are also tape-less video cameras that use compact flash storage.