After restoring a database from a production environment to a development environment (to get current data) I am trying to drop some production service accounts after which I will introduce their corresponding development service accounts.  For some unknown reason the simple command DROP USER [userName] is taking exceedingly long.

The account has no special schema nor does it own any object nor is it dbo (although it once was).  All its permissions are revoked and it has no references in either server_permissions or database_permissions.

When the command is invoked (drop user [userName]) it just goes off forever.  After 1:40 (tha's an hour and 40 min) I killed it and it took maybe ten minutes to "roll back" whatever was rolling back (?).  I just started another attempt and at the 23 minute mark this thing has racked up 27 million reads, 751 thousand writes, and 115 thousand physical reads in sp_whoisactive.  It is as though the drop is going through every page in the database.  What the heck.

Any idea on what the is taking so long?

• Edited by Lumley 15 hours 10 minutes ago clarifying time metric

Dropping a user does not toss for database scan. 

You need to ensure that there is no open session from the user who are connected to the server.You can find it sp_who2 or sys.dm_exec_requests or sysprocesses

If any, terminate all the sessions and then execute the drop statement.

-Prashanth

• Edited by Prashanth Jayaram 14 hours 57 minutes ago

Thank you for the response.  This user is not connected.  I am the only user connected.  The database is in restricted user mode with sql agent turned off.

I should have added, the users does not appear in either sp_who2 nor the exec_requests.  This is a service account used by Service Broker to communicate between two servers.  All the infrastructure for cross-server Service Broker communication has been torn down (otherwise the drop user command would error on there being objects owned by the account).  I have even removed all the certificates associated with the account.

Still stumped.

• Edited by Lumley 14 hours 22 minutes ago

It should not take this much time. There is one such article found explaining such scenario but it just taking 3 seconds.

http://connect.microsoft.com/SQLServer/feedback/details/694498/drop-user-takes-too-long-time-3-seconds

What is the sql version you are using? 

-Prashanth

I would use beta_lockinfo to get an idea of what is going on. From what you describe there should be tons of locks. And what is locked could give a clue.

You find it on my web site:
http://www.sommarskog.se/sqlutil/beta_lockinfo.html

Thanks all, and thanks Erland.  Awesome utiltity there.  I think it shows me where the issue is but it may take me a while to understand what to do about it.  It appears that all the locking is based on Service Broker parts and pieces.  (So I think I need to now go and see if there are things in the transmissions queues. etc. that need to be dealt with).

As an example:

count     object                                 rsctype       locktype  lstatus ownertype        rscsubtype
525919  MediaSearch                      METADATA   Sch-M      grant   TRANSACTION  CONVERSATION_ENDPOINT_RECV

525595 MediaSearch.sys.sysdercv KEY              X              grant  TRANSACTION

So it seems that you have a lot of oprhaned conversations on the server. I guess that a SELECT on sys.conversation_endpoints returns tons of rows?

You can use this to get rid of them:

   SELECT 'BEGIN TRY
          END CONVERSATION ''' + convert(char(36), ce.conversation_handle) +
          ''' WITH CLEANUP END TRY BEGIN CATCH END CATCH'
   FROM   sys.conversation_endpoints ce
   JOIN   sys.services s ON ce.service_id = s.service_id