sp_estimate_data_compression_savings hung and wouldn't rollback - by Derek Czarny

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 833339 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 3/13/2014 6:26:19 AM
Access Restriction Public

Description

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 Microsoft on 12/1/2014 at 9:18 PM
Derek,

I am closing this as no-repro. My recommendation is to open a case with CSS. They will also need a repro to identify the issue

Thanks
Sunil
Posted by Microsoft on 11/12/2014 at 10:29 AM
Derek:

Please let us know if you are able to open the case with CSS.

thanks
Sunil
Posted by Microsoft on 11/12/2014 at 10:28 AM
Derek:

My recommendation is to open a case with CSS. They will most likely ask you to get a SQL dump to understand what is going on. I have not been able to repro this issue. For this reason, I am closing this as a no-repro but with a recommendation to contact CSS

thanks
Sunil
Posted by AllenYW on 10/8/2014 at 2:39 PM
It happened on SQL 2008 R2 version as well. During rollback, it blocks itself on a hard coded temp table called #sample_tableDBA05385A6FF40F888204D05C7D56D2B, which involves in nested cursors with many dynamic DDL statements (alter indexes and constrains on this table). Lock type is Sch-M.
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
Derek,

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.

thanks
Sunil