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.