I am trying to encrypt a number of tables (1 column in multiple tables) in two different databases. On Database A I manage to encrypt these, while on database B it fails with an error: 'Unable to reconnect to database: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.'. Have tried this on another staging server and got the same result so issue seems to be database related and not server related. Tried updating statistics and restating the entire machines however problem persists. Also no activity is going on the staging server so should not be related to locks. Any suggestions?

Thanks in advance.

  • 1
    It's probably data size-related, not database- or server-related. Are you using a wizard in a local copy of Management Studio against a SQL Server instance in some other location? Maybe try doing that using SSMS on a jump box or the server itself. Did you look at what the query is waiting on or if it's being blocked? – Aaron Bertrand Feb 18 at 16:22
  • Hi Aaron, thanks for your reply. It shouldn't be size related because table has aprox 500 rows with 4 columns. Have tried using SSMS locally and from a jump box. Also through Powershell script but still same results. Any other ideas? Thanks in advance. – Ron Feb 19 at 11:59
  • Yes, look in sys.dm_exec_requests while the query is running to see what it’s waiting on (including if it is being blocked by another process). – Aaron Bertrand Feb 19 at 12:40
  • Checked again. Nothing blocked. Ordered result by wait_type and this was BROKER_TRANSMITTER at 700110996 – Ron Feb 20 at 12:34
  • You need to focus on that session, ordering by wait type alphabetically for all sessions doesn't help you troubleshoot that session. Try it again and filter that view to that session_id, and also look at sys.dm_exec_session_wait_stats for just that session_id. – Aaron Bertrand Feb 20 at 13:12

Your Answer

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

Browse other questions tagged or ask your own question.