locked
metadatalock RRS feed

  • Question

  • Hi,

    Please, I need some assistance.

    I get this error when using my admin user in SQL 2012:

    70

    1

    admin

    Database_Name

    SUSPENDED

    UNKNOWN TOKEN

    131447

    LCK_M_SCH_M metadatalock subresource=DATABASE_PRINCIPAL classid=principal_id = 194 dbid=5 id=lock771f6d200 mode=Sch-S


    69

    16

    ServerName

    internal

    When I look at the command it is: "EXEC sp_addrolemember 'Role_Admin', 'admin';"

    Anyone know what could cause it?







    • Edited by Johan_za1 Thursday, December 6, 2018 5:06 PM
    Thursday, December 6, 2018 5:03 PM

Answers

  • Solved it. It seems that the Server Audit and Server Audit Specifications caused this problem. I removed it and all was well again... Thanks.
    • Marked as answer by Johan_za1 Wednesday, December 12, 2018 2:15 PM
    Wednesday, December 12, 2018 2:15 PM

All replies

  • You talk about getting an error, but the output you post seems to come from query. So it is difficult to understand the situation correctly. I guess 70 is a spid, but is this the spid of your process or something else?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 6, 2018 10:38 PM
  • Hi Johan_za1,

     

    I am also not clear about your description. Could you please share us more details about your scenario.

     

    What's error you get? And which statement you ran to get the result you posted?

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 7, 2018 3:17 AM
  • Sorry.

    When executing this: EXEC sp_addrolemember 'Role_Admin', 'admin';

    I get this message when looking at the Activity Monitor:

    

    Session ID User Process Login Database Task State Command Application Wait Time (ms) Wait Type  Resource ID that is Blocking this Task Returns 1 if  Current Memory in use by this session Host Name Name of ResourceGoverner
    70 1 admin Database_Name SUSPENDED UNKNOWN TOKEN   131447 LCK_M_SCH_M  metadatalock subresource=DATABASE_PRINCIPAL classid=principal_id = 194 dbid=5 id=lock771f6d200 mode=Sch-S 69   16 ServerName internal

    Hope it makes better sense.

    Cheers.

    right-click and select open in new window to view the screeshot.






    • Edited by Johan_za1 Friday, December 7, 2018 4:01 PM
    Friday, December 7, 2018 10:24 AM
  • You seem to be blocked by spid 69, so next step would be to see who that is and what it is doing.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Bob_FT Monday, December 10, 2018 3:35 PM
    • Unproposed as answer by Johan_za1 Wednesday, December 12, 2018 2:14 PM
    Saturday, December 8, 2018 4:37 PM
  • Hi,

    in what database context do you try to run the query to add the user to the role.

    Are you able to change to master or msdb database:

    USE master
    GO
    EXEC sp_addrolemember 'Role_Admin', 'admin';


    Kind regards,

    Andreas

    Wednesday, December 12, 2018 8:26 AM
  • Solved it. It seems that the Server Audit and Server Audit Specifications caused this problem. I removed it and all was well again... Thanks.
    • Marked as answer by Johan_za1 Wednesday, December 12, 2018 2:15 PM
    Wednesday, December 12, 2018 2:15 PM