Spatial Index – No catalog entry found for partition

Last week, I came across a problem with spatial indexes. Apparently there’s still a bug in SQL Server 2012 (the SQL Server team thought they’d fixed it in 2012), when building an index and using the DMVs after that.

I tried to build an index on a world map. This map contained all the country, province/state and city information of the whole world. We needed the data to feed a new company portal for our customers. To speed up the queries we ran on the data, I tried to add a spatial index. To see which index-setting worked best for us, I used the sp_help_spatial_geography_index DMV (Dynamic Management View) to look at performance statistics. This worked fine, untill I tried a number of different index setups. Then I got this error:

Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594041073664 in database 9. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.

The first thing I did was search for the error number and specific issue. Apparently there were few problems documented. Eventually I ended up with a Microsoft Connect issue, started by my colleague and friend Bob Beauchemin (Blog | @bobbeauch).

After contacting and consulting Bob, I tried to install SQL Server 2012 CU1 (Consecutive Update) and CU2, and after both updates the error still occurred.

The conclusion is that the SQL Server team is trying to fix this in the next big update. Looking at the status of the Connect item, I personally don’t see this happening in a hotfix.

In the Connect issue I also posted a workaround. Because the building of the index is working, and only the DMV isn’t doing what it’s supposed to do, you can just restart the SQL Service and the DMV will work again. This isn’t a fix for production servers, but I assume you will only create an index once, and then the DMV will work!

UPDATE

With the release of SQL Server 2012 SP1 (announced at the PASS Summit 2012), this bug has been solved. You can download SP1 here. To see what’s new in SP1, read this MSDN article.