Home Dashboard Directory Help

sp_estimate_data_compression_savings hung and wouldn't rollback by Derek Czarny



Sign in
to vote
Type: Bug
ID: 833339
Opened: 3/13/2014 6:26:19 AM
Access Restriction: Public
User(s) can reproduce this bug


I ran sp_estimate_data_compression_savings on 2 different SQL Server 2012 sp1. One had CU8 and the other had base sp1. On both servers this stored procedure hung. I ran it for one table and one index and left it running for 12 hours. When I tried to rollback the procedure, it would never rollback. I had to finally reboot the server to get it to clear out. I noticed that the process was running in parallel and trying to get a LCK_M_SCH_M.
Sign in to post a comment.
Posted by Steinar Andersen - SQL Service on 5/23/2014 at 5:59 AM
Forgot to add:
The SPID (51 in this case) is blocking itself in tempdb as per this output from sp_lock:
spid    dbid    ObjId    IndId    Type    Resource    Mode    Status
51    5    0    0    DB                                        S    GRANT
51    2    34    0    TAB                                        IX    GRANT
51    2    34    1    KEY    (885516b8ae4c)                     X    GRANT
51    2    -1372702594    0    TAB                                        Sch-M    GRANT
51    2    -1372702594    0    TAB                                        Sch-M    WAIT
Posted by Steinar Andersen - SQL Service on 5/23/2014 at 5:57 AM
I have the same problem. Same results if I run it in QA or in GUI
SQL Version info: Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
    Oct 25 2013 19:04:40
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

The database in questin is a Dynamics CRM 2011 db
Posted by Derek Czarny on 4/21/2014 at 12:13 PM
I could but this system is now a production system, so I don't even want to try it. I could see if it happens in QA. what will you need me to collect to get the information you need?
Posted by Microsoft on 4/21/2014 at 10:48 AM

We have not seen this kind of an issue with sp_estimate_data_compression_savings. Is it possible to for you to give us a repro.

Sign in to post a workaround.