1

I manage a virtual SQL server that holds several databases for some applications that run in a front-end server. The issue I'm going through is that, at a specific moment during the day, the server gets stuck and the instance becomes unreachable. I can RDP the server but I cannot connect neither from SSMS nor from the application. I have searched into the OS logs and into the SQL server error logs but I find nothing relevant that could give a hint about what is causing the SQL server to become unresponsive. As a consequence, I have to do a stop-and-start process with the SQL server service and after that the instance will become available once again.

Anyone who might have experienced this in the past? Thanks.

  • 1
    What does "cannot connect" mean? Do you get an error message? What is it? Did you try connecting using the DAC, and/or from sqlcmd or SSMS on the server, just to rule out any port-related issues? – Aaron Bertrand Feb 8 '17 at 21:31
  • Sorry I had not provided any updates but I'm still going through the issue. I enabled the DAC connection and was testing it so when I came across the issue I could be able to find out what is causing the server to not server more incoming queries. Thing is that I'm not able to use the DAC and got some error about "a name that is no longer available". I have used the FQDN name, IP address and localhost just to make sure that it is not about resolving names. I have screenshots of the errors if that can help, just let me know how I can upload them. Thanks for your time. – Humberto Castellon Feb 14 '17 at 20:51
  • When you get an error message, you can copy and paste it. Screen shots are big and unsearchable. – Aaron Bertrand Feb 14 '17 at 21:04
  • A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64) – Humberto Castellon Feb 14 '17 at 23:11
  • Sounds like flaky/unreliable DNS (might be an overloaded primary domain controller, or using an external DNS service), and you should bring this up with Windows admin(s). Does this happen if you use an IP address in your connection strings instead of a name? – Aaron Bertrand Feb 14 '17 at 23:15
3

Try running sp_Blitz, a free health check for your SQL Server (disclaimer - I'm the author). You can also run it with @OutputType = 'markdown' if you want to share the results here at Stack.

I've got a hunch that you're running into THREADPOOL waits, and sp_Blitz alerts about that. THREADPOOL means your SQL Server ran out of worker threads to service incoming queries. It won't show up in the OS or SQL Server error logs.

When it's happening, you'll be able to connect to SQL Server using the Dedicated Admin Connection (DAC) (disclaimer: that's a blog post on my site.) The DAC is a set-aside CPU scheduler used just for emergency troubleshooting. From there, you'll be able to see which queries are burning up all the worker threads - typically it's a blocking problem.

  • Ninja'd as I posted a link to your blog post as well! – CaM Feb 8 '17 at 21:36
  • HAHAHA, nicely done. – Brent Ozar Feb 8 '17 at 21:39
  • Thanks Brent, I will try that out and will post as soon as I find what is causing pain on the server. Kudos to you. – Humberto Castellon Feb 8 '17 at 21:49
  • Sorry I had not provided any updates but I'm still going through the issue. I enabled the DAC connection and was testing it so when I came across the issue I could be able to find out what is causing the server to not server more incoming queries. Thing is that I'm not able to use the DAC and got some error about "a name that is no longer available". I have used the FQDN name, IP address and localhost just to make sure that it is not about resolving names. I have screenshots of the errors if that can help. Thanks for your time. – Humberto Castellon Feb 14 '17 at 20:31
  • @HumbertoCastellon there are no screenshots in your question. You can also try using the DAC locally from the desktop itself. – Brent Ozar Feb 14 '17 at 21:26
0

The first thing you need to do is set up SQL Server so you can get in during the lock-up.

Brent Ozar explains the Remote Dedicated Admin Connection here. Turn that on so you can have an administrative "Back door" that ought to be up even if nothing else is.

From there, try to see what processes are running during the lock-ups. Perhaps there's something in the sys.dm_exec_sessions or sys.dm_exec_connections views that can point to what's locking, assuming the database knows what's going on.

You might also set up a scheduled job to run this query once every 5 minutes during business hours (or whenever the lock-ups occur), and either send you the data or store it in a table somewhere.

 select     
@@servername, sess.session_id, sess.login_time, 
sess.host_name, sess.program_name, sess.login_name, 
sess.nt_domain, sess.nt_user_name, sess.status, 
requ.status, sess.last_request_start_time, 
sess.last_request_end_time, sess.is_user_process,conn.connect_time,
requ.start_time, sess.cpu_time, requ.cpu_time,
sess.memory_usage, sess.reads session_reads, sess.writes, 
sess.logical_reads, conn.num_reads, conn.num_writes, 
conn.last_read, conn.last_write, requ.blocking_session_id, 
requ.wait_type, requ.wait_time, conn.client_net_address, 
requ.command, db1.name, sql1.text, sql2.text 
from sys.dm_exec_sessions sess
left join sys.dm_exec_connections conn on conn.session_id = sess.session_id
left join sys.dm_exec_requests requ on requ.session_id = sess.session_id
outer APPLY sys.dm_exec_sql_text(requ.sql_handle) sql1
outer APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) sql2
left join sys.databases db1 on db1.database_id = requ.database_id

From that, you might see if something shows in the blocking_session_id column or wait_type columns near the time of your error.

Also don't forget to check your WINDOWS event logs on the server. Sometimes SQL Server events are written there, as well.

0

If you have a specific time of the day when it gets stuck, a simple SQL Profiler trace should help identify what processes are being executed at that time.

My suggestion would be to enable the default trace for 10 minutes or so before the specific time you are experiencing the issue, then disable a few minutes after, and examine the trace file. It may hurt performance a little bit for the time period, but seems that shouldn't be much of a concern at this time.

  • 1
    If the server is running into real performance issues like with THREADPOOL or a RESOURCE_SEMAPHORE wait, the trace won't be able to run to collect data either. It's fighting for the same resources, and may make things worse. – Erik Darling Feb 8 '17 at 22:15
  • Worse performance than "stuck" doesn't seem possible. Note the OP is not describing a performance problem, but a complete stoppage that requires a restart. Tracking what was the last activity before stuck is part of the necessary debugging, of course not a guaranteed solution. – Bruno Guardia Feb 8 '17 at 23:32
  • Sounds like you've got it under control. Best of luck. – Erik Darling Feb 8 '17 at 23:53

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.