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.

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


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 James Lupolt on 3/31/2016 at 3:43 AM
I believe you'll see blocking and/or deadlocks when running sp_estimate_data_compression_savings concurrently on different tables because of the same constraint names being used for different temp tables in TempDB. The code I've seen getting blocked is the following, which I think is probably coming from sys.generate_table_sample:

alter table [#sample_tableDBA05385A6FF40F888204D05C7D56D2B] add constraint [sample_indexDBA05385A6FF40F888204D05C7D56D2B_clustered_pk] primary key clustered ([fooid] asc)

This isn't a huge problem for me. I just call sp_estimate_data_compression_savings serially instead.
Posted by Microsoft on 12/1/2014 at 9:18 PM

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

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

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

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

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

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

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.