The USS Acadia is now marine habitat, sunk in mid-September, 2010.  You can find details about it here. The Acadia was used for live fire exercises by the Air Force, Navy, and somewhat the Marines (the Marines' stuff didn't fire).

Gerald Soto posted pictures of the sinking in the photo gallery; kind of wild to see.  One would hope they atleast intend to recycle the metal.  Steel is like gold these days if you have to buy it. The screw is probably rather pricey too.