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.

8 Responses to Spatial Index – No catalog entry found for partition

  1. Ashish Jaiswal says:

    I got the same error in SQL server 2012 SP1. Can anyone have any update on this.

    • DevJef says:

      The index does actually work, except you can’t use the DMV’s. A restart of the SQL Server service does the trick, like mentioned in the Connect item. But that’s not always an option. Certainly not if you’re testing different types of indexes to figure out what best suits your needs.

      Do you run a slipstreamed version of SQL 2012 with SP1? Then you could try to reapply SP1 (if possible). If that doesn’t work, please let me know. Then we can update the Connect item, and I can contact some people to try and solve the issue.

  2. Ashish Jaiswal says:

    Hi Dev,

    Thanks for the reply. Actually this is the sharepoint production DB server and we don’t have the control on application side as what caused this error. We got this error only one time and later we did not receive this. But I am just curious what could be the reason for this error.

    Note: This is not slipstreamed version of SQL 2012 with SP1.
    Thanks in advance.

    • DevJef says:

      If I understand you correctly, you only saw this error once? This is because the spatial index does work. Only the DMV doesn’t work, because of a bug. After a restart of the SQL Service or the machine, the DMV works again. The only time you’ll get this error, is when you drop the existing index, and create a new one.

      If I remember correctly, I’ve created some demo code to replicate this issue to post with the Connect item. I’ll try to find it, and test it on SQL 2012 SP1. If I can reproduce it, I’ll contact Bob Beauchemin, and see if we can reopen the Connect item.

    • DevJef says:

      I’ve test this on SQL 2012 SP1 this morning, and I don’t get the error anymore. Could you check the build number of the SQL Server you get the error on, and mail me the output? I can mail you back the example query I made for the Connect item if you’d like.

      • Ashish Jaiswal says:

        Hi Dev,

        Below is the build number:

        Microsoft SQL Server 2012 (SP1) – 11.0.3339.0 (X64)

        Please mail me example query so that I can test in my environment.

        Thank You.

      • DevJef says:

        I’ve tried to send you a mail with some files and steps to follow, but the mail bounced. Please send me your e-mail address, so I can send you the files you need.

  3. Ashish Jaiswal says:

    HI Dev..
    You can send files on below mail id:
    aniish2006@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: