Post reply

Problem with truncate not releasing exclusive access

  • May 20, 2013 at 3:26 pm

    #394813

    I am truncating several tables using a SQL script. The script runs successfully. The next thing I do is a restore to the DB using either the Management Studio or a restore script. (I'm restoring from a production DB backup). In either case, I get an error on restore "Exclusive access could not be obtained because the database is in use."

    It appears that the truncate still has exclusive access.

    Am I forgetting to do something as part of the truncate?

    I am running all commands as sa (which is the db_owner)

    Example:

    use MyTestDB

    truncate table dbo.my_employee_list;

    go

    truncate table dbo.my_employee_pay;

    go

    thank you in advance for your help

  • May 20, 2013 at 4:41 pm

    #1616811

    You need to close the query window from which you ran the truncate (or change the database context to some other database). It's not that truncate is holding any exclusive access, it's not. It's that the restore cannot get exclusive access because there are open connections, including the one you ran the truncate from.

    Close all query windows, make sure that object explorer is not on that database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 21, 2013 at 7:18 am

    #1616993

    Thank you so much.

    This worked perfectly.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply