The tragic hero can take on many different shapes and forms, depending on the tone of the game they appear in. For the most part they seem to conform to two basic archetypes: the bad-guy turned good and the good-guy turned bad. The bad-guy turned good seeks redemption in spite of his past wrongdoings, often with the knowledge that he won't actually be able to achieve it. The good-guy turned bad begins as a relatively moralistic individual who suffers great personal losses in their life and, as a result of their suffering, adopts a morally ambiguous world-view and resorts to methods which would otherwise be unacceptable in a mainstream "good" protagonist. Generally both of these character-types spend most of the games in which they appear seeking either revenge or redemption.
Starkiller, from the Lucasarts game the Force Unleashed, lost his parents at a young age and spends most of the game slowly drifting from dark to light as he seeks to redeem himself in light of his past wrongdoings; he is a bad-guy turned good who meets a tragic end. John Marston of Red Dead Redemption fame also fills this role. He seeks to make amends for a life of murder and thievery, knowing full well that he likely doesn't deserve forgiveness and will surely fail in the end. Max Payne (star of the games which bear his name) on the other hand begins as a upstanding, responsible, heroic figure, and is plunged into a dark, shady, corrupted world after the death of his wife and child. His aim is to get revenge on those who he felt wronged him by whatever means he deems fit. His arc is that of the good-guy turned bad who no longer believes that he deserves a happy ending.