SSMS: First connection to DBMS takes over 15 minutes

Question

Post reply

SSMS: First connection to DBMS takes over 15 minutes

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ManicStar SSCoach Points: 17992 More actions · Answer 1

Is the DB in an availability group and are you connecting to the listener vs directly to where the primary replica is?

We've seen occasional timeouts connecting through the listener to the primary replica on the first connection.

farside41 SSCrazy Points: 2083 More actions · Answer 2

Eric M Russell (1/8/2016)
This type of behaviour would be situation normal for a Java app, but not for a Windows executable. Using SysInternals Process Explorer, see what SSMS.EXE is doing during the several minutes of connection deplay. For example, it is chewing up a lot of CPU? Also, load Process Monitor, filter events by SSMS, and see what file activity is going on.
https://technet.microsoft.com/en-us/sysinternals/bb795533

Tried running Process Explorer, it's not showing much of anything it from that perspective it just looks like SSMS.exe is idle, not using more than 1% of CPU and not much memory.

I'll have to try Process Monitor

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

You could try running SSMS with the /log switch.

This will write a log to your local user profile under %AppData%\Microsoft\AppEnv\10.0 called ActivityLog.xml

If you save the attached XSL file in the same folder and open the XML in Internet Explorer, you might be able to identify what is happening during the pause...but it might not help...

Does the delay happen for all network protocols?

Does it happen if you don't have SSMS open Object Explorer automatically and you just open a query window? vice versa?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

farside41 SSCrazy Points: 2083 More actions · Answer 4

Eric M Russell (1/8/2016)
This type of behaviour would be situation normal for a Java app, but not for a Windows executable. Using SysInternals Process Explorer, see what SSMS.EXE is doing during the several minutes of connection deplay. For example, it is chewing up a lot of CPU? Also, load Process Monitor, filter events by SSMS, and see what file activity is going on.
https://technet.microsoft.com/en-us/sysinternals/bb795533

Just tried using Process Monitor, and for the bulk of the 15 minutes it seems to be just sitting idle and not doing anything. It does some file operations sporadically, but nothing that takes more than a couple milliseconds to complete.

How do I start SSMS.exe with the /log switch, I am not familiar with this.

EDIT: I should also add that if I filter by duration and look for operations taking longer than 1 second, I am not spotting anything that looks related.

bramsey 85298 Right there with Babe Points: 767 More actions · Answer 5

I did not see this post before creating my own here:

http://www.sqlservercentral.com/Forums/Topic1760484-391-1.aspx?Highlight=initial+connect

I am having the exact same issue, and have tried all the same steps you have, but I cannot figure this out and am considering a full pc wipe.

I noticed i have this problem even if I am not connected to the network where the server instance resides. I click connect and wait 15 minutes for it to fail. i then plug into the network, and click connect and it is instant.

This not only affects my ssms, but also any .net app that tries to connect such as our custom c# apps.

I too see nothing out of the ordinary in logs, and it appears no attempt is even made to actually connect until that 15 minutes is up.

Started for me about 2 weeks ago.

farside41 SSCrazy Points: 2083 More actions · Answer 6

One workaround that I found (Although I still haven't found the solution) is that if I run SSMS as Administrator it works as expected.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

Also, confirm if there are any login triggers.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

bramsey 85298 Right there with Babe Points: 767 More actions · Answer 8

Interesting, running with elevated admin privileges does seem to bypass the issue. It also raises more questions. What on earth is the computer trying to do for 15 minutes that it finally gives up on and allows the connection to happen... seems like a timeout, but there is no error given.

My head hurts.

Thanks,

-Bo

farside41 SSCrazy Points: 2083 More actions · Answer 9

I have no idea, it's a pretty frustrating issue, it does make me wonder if something isn't right with my laptop.

I have been wanting to follow up on this with the desktop team for weeks but haven't been had time to.

And the workaround has taken away the sense of urgency.

farside41 SSCrazy Points: 2083 More actions · Answer 10

Just happened to see another post about this was at the top of the page:

http://www.sqlservercentral.com/Forums/Topic1660437-2799-1.aspx

One poster there mentioned clearing the MRU, I did that and it looks like it fixed me without having to run as administrator.

Here are the steps I used:

https://www.mssqltips.com/sqlservertip/2319/steps-to-clear-the-server-list-in-sql-server-management-studio/

bramsey 85298 Right there with Babe Points: 767 More actions · Answer 11

YES!!!

Instructions slightly different for me because I'm using SSMS 2014

Close SSMS

Go To

%APPDATA%\Roaming\Microsoft\Microsoft SQL Server\120\Tools\Shell

delete the RegSrvr.xml file

open SSMS

Fixed!

As a side note... the previous RegSrvr.xml and the new.... are completely identical, and for me it did not clear my server list or change any settings I can find. My guess is that simply forcing SSMS to recreate this file means it also fixes the issue elsewhere... I won't argue with the result, but it doesn't make any sense to me that it works lol.

Thank you!

-Bo

bramsey 85298 Right there with Babe Points: 767 More actions · Answer 12

Spoke to soon...

After it seemed to work for a day. Now I am back to the same behavior. I have tried deleting the file again using the same instructions, and now that does not help either.

Elevated privileges do still work around the issue, but I'm still looking for a permanent solutions.

farside41 SSCrazy Points: 2083 More actions · Answer 13

bramsey 85298 (4/22/2016)
YES!!!
Instructions slightly different for me because I'm using SSMS 2014
Close SSMS
Go To
%APPDATA%\Roaming\Microsoft\Microsoft SQL Server\120\Tools\Shell
delete the RegSrvr.xml file
open SSMS
Fixed!
As a side note... the previous RegSrvr.xml and the new.... are completely identical, and for me it did not clear my server list or change any settings I can find. My guess is that simply forcing SSMS to recreate this file means it also fixes the issue elsewhere... I won't argue with the result, but it doesn't make any sense to me that it works lol.
Thank you!
-Bo

I don't think RegSrvr.xml is the right file, that would be the file that contains all your registered server, not the recent server list.

bramsey 85298 Right there with Babe Points: 767 More actions · Answer 14

I tried removing the entire folder structure for SSMS in the appdata folder, and it still makes no difference. Honestly the file I mentioned is the only file in that entire folder structure.

Perhaps my path is wrong?

\AppData\Roaming\Microsoft\Microsoft SQL Server

farside41 SSCrazy Points: 2083 More actions · Answer 15

bramsey 85298 (4/25/2016)
I tried removing the entire folder structure for SSMS in the appdata folder, and it still makes no difference. Honestly the file I mentioned is the only file in that entire folder structure.
Perhaps my path is wrong?
\AppData\Roaming\Microsoft\Microsoft SQL Server

For SQL 2012 I renamed this file: %APPDATA%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin