Truncate table doesn't release LCK_M_SCH_S

Question

I have a stored procedure which truncates a not so large table (2M records but it will get bigger in the future) and then refills it. The sample version is like below:

ALTER PROCEDURE [SC].[A_SP]
AS
BEGIN

BEGIN TRANSACTION;

BEGIN TRY
    TRUNCATE TABLE SC.A_TABLE

    IF OBJECT_ID('tempdb..#Trans') IS NOT NULL DROP TABLE #Trans

    SELECT 
        *
    INTO 
        #Trans
    FROM
    (
        SELECT
            ...
        FROM 
            B_TABLE trans (NOLOCK)
        INNER JOIN
            ... (NOLOCK) ON ...
        LEFT OUTER JOIN
            ... (NOLOCK) ON ...
        ...
    ) AS x

    INSERT INTO
        SC.A_TABLE
        (
            ...
        )
    SELECT
        ...
    FROM
        #Trans (NOLOCK)

    DROP TABLE #Trans
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
END

This procedure takes a few hours to work. Sometimes I want to take a COUNT to see how much is finished by using:

SELECT COUNT(*) FROM A_TABLE (NOLOCK)

This doesn't return anything (even with NOLOCK) because there is LCK_M_SCH_S lock on the table because of TRUNCATE statement. I even can't do:

SELECT object_id('SC.A_TABLE')

The other interesting thing is; I sometimes stop the execution of the procedure through SSMS and even after that I can't take a COUNT or select it's object_id. The execution seems suspended in sys.sysprocesses and I have to close the query window to make it release the lock. I suspect it's because I use transactions and leave it in mid state by stopping the execution but I'm not sure.

I know that truncating the table doesn't take so much time since the table doesn't have any foreign keys or indexes.

What can be the problem? I may use DELETE instead of this but I know that TRUNCATE will be much faster here.

EDIT: DELETE instead of TRUNCATE works without any problem btw but I only want to use it as a last resort.

Curious, you're using truncate for speed (as its not logged), but you put it in a transaction... Does that not make it logged? Do you need a transaction here since you're routinely obliterating the table and can easily replace it? Can you use SSIS instead, its bulk load is way faster than INSERT INTO (twice). — Dave C, May 25 '17 at 15:03
Interesting, it seems truncate in a transaction logs a page de-allocation instead of a delete... https://stackoverflow.com/questions/1522931/truncate-table-within-transaction — Dave C, May 25 '17 at 15:06
@JiggsJedi Exactly, this procedure is called by another procedure and that one is called from another etc. and it is transactional end to end. — yakya, May 25 '17 at 15:09
Well, you're stuck if you don't change your requirements. Since you are replacing the entire table, it is probably safe to assume that losing all the data in it is acceptable even if the insertion logic fails. If so, move the truncate out of the transaction. More concerning is that the process for inserting 2 million rows takes such a long time - but apparently that part doesn't worry you. And you can probably lose the "nolock" hints; it is highly likely they serve no useful purpose nor are their risks understood. — SMor, May 25 '17 at 15:10
@sotn you can call an SSIS Package inside a stored procedure... — Dave C, May 25 '17 at 15:16
@SMor Truncates are rolled back when an exception occurs, it would be a problem if I move it outside of the transaction. I don't want to lose the data if the process fails because of some important reasons. Query takes so much time because I have lots of joins etc. but it will work much faster in production environment because of the so much better hardware and other scalability features. And "nolock" s are company policy, can't change that :) — yakya, May 25 '17 at 15:17
@JiggsJedi Unfortunately, I can't use SSIS because of company rules too.. — yakya, May 25 '17 at 15:22
Well... you're doing 2M rows at a time (twice) instead of a batched insert, inside a transaction... I would eliminate the temp table, and insert directly into the A_TABLE, and batching it into 4K chunks might be your best bet. — Dave C, May 25 '17 at 15:24
@JiggsJedi I need to inspect the query execution plan between the two because the reason for me to split these inserts was these 2 queries are actually too complex (it seems so trivial in the question but trust me :) ) and I thought that query execution plan may not handle it very well. — yakya, May 25 '17 at 15:30

score 1 · Accepted Answer · answered May 26 '17 at 10:32

1

If Truncate ain't your bag and you have way too many rows for a Delete to execute without bring the TLog to a crashing standstill, there's always option UbW (for Ugly, but workable): Create a clone of the table, load the rows into that, then (and inside a transaction), switch everything around.

Option UbW2 builds on that concept - have two tables always built - one empty, one full. Load into the empty table and then modify a View of Synonym to point to that table.

Option LUbW (less ugly...) involves using partitions: Load your data into a switch table then move that as a partition using some flag as your partition function.

All of these require more work and code. We have similar situations and use option UbW2 for our data warehouse that allows us to load millions of rows into 'active' tables with no downtime every hour, nor risking the consumers seeing inconsistent data.

answered May 26 '17 at 10:32

Rachel Ambler

1,440
12
23

UbW2 is actually a pretty nice way to go in these kind of situations I guess. I simply can change to `DELETE` instead of `TRUNCATE` in this case but if the situation were more complex than this, I would go with UbW2. And I don't think that they are ugly :) (thanks for that abbreviation btw, good one) I didn't quite understand LUbW though. Why do you think that this is less uglier? It seems similar to UbW2. The difference is in one of them I'll alter the view and in the other one I need to flip this flag in partition function everytime I run this SP right? – yakya May 26 '17 at 10:52
Less Ugly because you don't have to worry about a 'pointer' View or Synonym pointing to the live table because you do you switch in and out in a transaction and so the table IS the source. Otherwise the View or Synonym becomes the source that consumers read from. In addition any synonym or View change has to wait until all consumers are out. Or warehouse has ~200 tables and it takes about 2 minutes to switch all the synonyms around. If I were doing it again I'd probably go for the last option myself, but our solution is VERY workable and stable. – Rachel Ambler May 26 '17 at 10:56

score 0 · Answer 2 · answered May 25 '17 at 18:41

0

Best you're going to get is to shift some of the heavy lifting out the transaction. That said, Sql Server is working 100% as per design.

ALTER PROCEDURE [SC].[A_SP]
AS
BEGIN

IF OBJECT_ID('tempdb..#Trans') IS NOT NULL DROP TABLE #Trans

    SELECT 
        *
    INTO 
        #Trans
    FROM
    (
        SELECT
            ...
        FROM 
            B_TABLE trans (NOLOCK)
        INNER JOIN
            ... (NOLOCK) ON ...
        LEFT OUTER JOIN
            ... (NOLOCK) ON ...
        ...
    ) AS x

BEGIN TRANSACTION;

BEGIN TRY
    TRUNCATE TABLE SC.A_TABLE   

    INSERT INTO
        SC.A_TABLE
        (
            ...
        )
    SELECT
        ...
    FROM
        #Trans (NOLOCK)

    DROP TABLE #Trans

    If @@TranCount >0 And Xact_State() = 1
        Commit Transaction;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW
END CATCH

answered May 25 '17 at 18:41

Rachel Ambler

1,440
12
23

But this one will still put that schema lock on the table right? I guess as long as I use `TRUNCATE`, I will face the issue. It's better to use `DELETE` in this condition I guess. Because `DELETE` puts row locks instead of schema lock and my queries which uses `NOLOCK` clause will work without problem.. – yakya May 26 '17 at 06:44
And I noticed that you added `Xact_State() = 1` control. Did you add it since it's a good practice or is there really a need for it in this query? Because I explicitly started the transaction at the start and the query is in `try..catch`. If something bad occurs it will go to `catch` clause and not try to commit the transaction. Could there be a case that `Xact_State` save my butt for this sp? – yakya May 26 '17 at 07:37
Xact_State() = 1 will make sure the transaction is committable (not all are). The @@TranCount is, in all honesty, probably redundant, since Xact_State() will only return 1 if there IS a transaction and it IS committable. But yes, it's always wise to check. Google it for more info. – Rachel Ambler May 26 '17 at 10:18
As the for SCH-M lock: Yes, and this is by design. When you perform a Truncate Table, behind the scenes Sql Server deallocates the tables pages. This deallocation is a serialized operation and, as such, requires the Sch-M lock. Since you're doing this in a Transaction that lock persists. Deleting is an option as long as there are not a large enough # of rows that would cause problems with the transaction log. – Rachel Ambler May 26 '17 at 10:27

Truncate table doesn't release LCK_M_SCH_S

2 Answers2