Yes it was & I agree, if he would have lived (mainly because L died) then that show would have been just epically ended, but of course I knew he was set to die when Ryuk told him that he would end up being the one to write his(Light/Kira's) name into his Death Note so when the time came I know that most good anime like this ends with the protagonist dying which lead me to believe that he would die somehow, just not that pathetically...

Regarding Death Note, the occurence and nature of Light's death was pretty appropriate. Him being toppled after creating a false image of himself as a god isn't just necessary morally, but logically as well. That pathetic pleading you see was his true nature all along - once stripped of his power and control over the situation, he could no longer keep his act up, and his true weak self was exposed. At that moment the "protagonist" you admired was already dead, so the character Light dying was no big deal. (L was much better anyway).

Anyway, enough about Death Note, as in my opinion it's not even that sad relatively.

If you want to go for tear jerking, I'd recommend checking out some works by Key (such as Angel Beats! and Air). Elfen Lied is another good one (though I wouldn't check this one out if you can't handle nudity and gore). If you're not up for a whole series, check out the movie Grave of the Fireflies for a quick dose of depression.