This phrase generally refers to two people or organizations fighting until both are reduced to very little. There are several versions of its origin.

One says that the people of Kilkenny, Ireland, actually did tie cats together by the tails and let them fight; the Irish cats and those from the walled "Englishtown" section apparently were supposed to particularly dislike one another.

Another version blames the cat-battles on Hessian soldiers stationed in Kilkenny after an Irish rebellion, "who amused themselves in barracks by tying two cats together by their tails and throwing them across a clothes-line to fight." When their officers tried to stop the pastime, one soldier cut the cats' tails off and the animals ran off. When the officer asked why there were bleeding tails on the ground, "he was coolly told that two cats had been fighting and had devoured each other all but the tails."

The less interesting story of the origin of the phrase has it as a metaphor for the way the cities of Kilkenny and Irishtown fought over their boundaries in the 1600s until both cities were much the poorer.

What ever the origin, the phrase was well known enough to inspire numerous 19th century political cartoons (especially those in England relating to "the Irish question") and this rhyme:

There was once two cats of Kilkenny
Each thought that there were two cats too many
So they fought and they fit,
And they scratched and they bit,
Till excepting their nails and the tips of their tails
Instead of two cats there weren't any.
The people of the city of Kilkenny are sometimes nicknamed "Kilkenny cats" as well; this is taken as a positive reference to the cats' refusal to give up.

In 1940, under the pseudonym of Kurt von Rachen, L. Ron Hubbard wrote a book called The Kilkenny Cats, part of a five-book series referred to by the same name. The name was also used by a 1980s rock band from Athens, Georgia, who released two albums, Hands Down in 1986 and Hammer in 1988.

Sources:
http://www.bartleby.com/81/3165.html
http://www.geocities.com/Athens/Forum/3041/kilkenny.html
http://www.kilkenny.ie/hist/heritage3.html
http://vassun.vassar.edu/~sttaylor/FAMINE/Punch/YI/KilkennyCats.html
www.people.virginia.edu/~dnp5c/Victorian/cats.html
http://www.sfsite.com/isfdb-bin/pseries.cgi?Kilkenny_Cats