The few times I have put something on the page and walked away knowing that I hit the mark were because of tears.
I see behind the veil, I know the ins and outs of the story itself.
If something I write makes me cry despite that knowledge, I know I have done something right.
Most character deaths in stories I love rarely make me cry. The exceptions are when the death itself is a completion of their character arc. I.E, Tony Stark in Avengers Endgame.
The times I've cried the most have always been character moments and are not motivated by sadness. No spoilers, but the second half of the Dresden Files are filled with these. Particularly books 15-17.
This may just be me and may lead to flaws in my stories, but a properly constructed scene that focuses on friendships or character growth is more apt to make my eyes wet than something that is explicitly sad.