To that end, Ubuntu is a great solution, with the only downside being that it's not very light by default. From my experience, a stock, Gnome-based Ubuntu system (regardless of the release) works best on machines faster than about 1Ghz -- a number that will depend on your tolerance for sluggishness. On computers slower than that, I find Gnome Ubuntu to be unbearable.
Kubuntu is about the same for me. And while Xubuntu has a reputation for being lighter than its Gnome or KDE cousins, it has matured into a rather hefty distribution. Xubuntu once promised a GTK2-based environment whenever possible, but now much of the Xubuntu framework is being replaced with Gnome counterparts, to ease development work and to satisfy the demands of its user base. Your experiences may differ, but from what I've seen, the threshold for Xubuntu is not much lower than straight Ubuntu.
So more often than not, the best solution for all these problems -- aged hardware, increased workload and bulky software -- is to start with the lowest possible level of function, and add to it. That means starting from the purest form of Ubuntu, and tweaking it to improve speed. Then you pick the core level programs you want, the fundamental software you want, and ultimately design a system how you want. In all, you could end up with a machine that performs faster than you ever expected.
Most of the tweaks in this guide are intended for machines that predate the Pentium 4 generation, but post-date the Pentium Pro. That means you'll see the best results on a machine between roughly 1Ghz and 150Mhz. If you're on a newer machine, you'll find that the innate speed of your hardware makes the tweaks pointless. In the same way, a machine slower than 150Mhz won't see much improvement -- the hardware is so slow that the tweak has no appreciable effect. But regardless of your hardware, give them all a try.