Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL SERVER – SSMS Database Expand Hang – High waits on PREEMPTIVE_OS_LOOKUPACCOUNTSID

Posted by blakhani on April 7, 2015


Recently I have had a friend who reported below issues.

  1. When I expand database, it takes a lot of time.
  2. When I expand Jobs under SQL Server Agent node in SQL Server Management Studio, it freezes and finally it fails with error  
    “An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”

The symptom on the server that we were working on was that from Management Studio, when trying to browse SQL agent job, the interface would hang. I have asked to capture Profiler trace to find out which query is taking time and what is the waits for those query which are stuck.

In profiler trace, and the query to capture currently running queries (refer this blog) I found that it was running sp_help_job stored procedure from MSDB database. This is the procedure to get high level details about all jobs in MSDB database using msdb.dbo.sysjobs_view. When we looked further, we found that SQL Server is running function dbo.SQLAGENT_SUSER_SNAME and got stuck at SELECT @ret = SUSER_SNAME(@user_sid) statement. The wait for the session is was PREEMPTIVE_OS_LOOKUPACCOUNTSID and wait time was increasing. This wait is related to the communication/validation from Active Directory.

When we debugged further, here is the chain of reaching to function.

sp_help_job  
             >> sp_get_composite_job_info 
                     >> Query having – owner = dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid) 
                                >>  SELECT @ret = SUSER_SNAME(@user_sid)

This is getting stuck at PREEMPTIVE_OS_LOOKUPACCOUNTSID. We was identified that the function is used to convert SIDs stored in SQL Server table to the name by making call to Active Directory. Now the challenge was to find why and also was to identify if its happening with particular logins or all login. The complexity here was that SQL Server stores SID in varbinary format not in the format which OS would understand.

Luckily, I have had a blog post having script to convert the varbinary to well known format. So I have used that to convert SIDs obtained from below query

select    owner_sid 
from    msdb.dbo.sysjobs_view
where    owner_sid <> 0x01

 

Once we have SID value in OS understandable format, I used PsGetSID tool from sysinternals to get Windows account name. While running that it was taking a long time and finally it failed with below error.

Error querying SID:

The trust relationship between the primary domain and the trusted domain failed

So, it was something to do with two domains trust which seems to be broken. I asked him to work with this Windows Domain Admin team and networking team to get the issue resolved.

Same issue might happen during database expand also as, database owner is a SID stored in sys.databases and that has to be converted to name.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Advertisements

    4 Responses to “SQL SERVER – SSMS Database Expand Hang – High waits on PREEMPTIVE_OS_LOOKUPACCOUNTSID”

    1. sHikha Tiwari said

      Informational Blog..

    2. Anil said

      Hi Balmukand Sir,

      Thanks for this blog post !!

      Just curious to know – In case database owner is changed from NTID (Win account) to some SQL login, AD won’t come into picture so will that allow users to expand database ?

      Thank you.

      Br,
      Anil

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: