5

In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement.

We use SQL Server 2012. In our code and queries in our stored procedures we don't make use of sp_tables.

How can I trace who is the responsible of this call? At least how can I log when this call happens?

I read that it's also called by third parties, in this case how can I track who is responsible?

Edit:

in our application we use Hibernate as ORM and c3p0 for connection pooling.

EDIT 2: we didn't succed in understanding when and by who the sp_tables was called but today we succeded in replicate the issue.

Seems that the sp_tables is called in responde to a failed delete tried on the schema.

The delete fails because there are other rows referencing the row that we try to delete, so we get an exception

SQLServerException: The DELETE statement conflicted with the REFERENCE constraint..

Is there evidence somewhere that this kind of errors triggers an sp_tables on the db?

  • 2
    It may be worth noting that SPIDs 1-50 are reserved for system processes. – RDFozz May 3 '17 at 19:20
  • 3
    When it's blocking you, you should be able to gather information about the source using sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_connections, etc. Or by running sp_WhoIsActive. – Aaron Bertrand May 3 '17 at 20:10
  • Tnx, good points. I will keep note of this next time we're blocked – frankieta May 3 '17 at 20:36
  • @frankieta, what you are getting error number? Did you follow "Aaron Bertrand" given instruction. – Md Haidar Ali Khan May 24 '17 at 10:38
  • @MdHaidarAliKhan no, I couldn't execute it because it was on a production db and they already had killed the sp_tables transaction. We are going to try replicate it in test – frankieta May 24 '17 at 11:06
2

You can set up an extended event session with following code. I tested this and it works (at least the way I was calling).

    CREATE EVENT SESSION [CallSP_tables] ON SERVER ADD EVENT 
    sqlserver.sp_statement_completed
   (SET collect_statement=(1)   
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,
    sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,
    sqlserver.plan_handle,sqlserver.query_hash) 
    WHERE ( [sqlserver].[like_i_sql_unicode_string]
            ([object_name],N'sp_tables') 
        ) 
    ), 
ADD event sqlserver.sql_statement_completed( 
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash)) 
ADD TARGET package0.event_file(SET filename=N'C:\callspTables.xel',
        max_file_size=(50), 
        max_rollover_files=(100)) WITH (max_memory=4096 kb, 
        event_retention_mode=allow_multiple_event_loss, 
        max_dispatch_latency=120 seconds, 
        max_event_size=0 kb, 
        memory_partition_mode=none, 
        track_causality=OFF, 
        startup_state=ON)
        GO
| improve this answer | |
  • we didn't succed in logging the sp_tables with extended events, but today we replicated the issue, so I updated my question. – frankieta May 24 '17 at 10:26
  • You can set up a test case and see if sp_tables get called. – SqlWorldWide May 24 '17 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.