In general, if you don't want to purchase a third party product, setting up server side traces and using the, free from Microsoft, RML Utilities.
But, if you want to spend money, I strongly recommend Red Gate's SQL Monitor. It's a great product and it's getting better all the time.