There was a problem indexing Product Flat Data
I deleted a whole lot of products and orders in Magento with a query. After I tried to reindex tables I got an error saying there was a problem reindexing data. Further investigation lead me to Magento community where I found a decent answer to my problem.
Seems that when deleting product data the Product Flat Data table didn't get updated. Also in the database i had not only 1 but 2 catalog_product_flat tables, catalog_product_flat_1 and catalog_product_flat_2 respectively. So my solution was to get rid of them both and then run reindex.
0. Backup database
1. Run SQL command in phpmyadmin:
DROP TABLE catalog_product_flat_1
2. SET FOREIGN_KEY_CHECKS=0;
DROP TABLE catalog_product_flat_2
3. In Magento admin, go to System -> Index Management and click Reindex Data next to Product Flat Data