Disconnect-DbaInstance does not actually disconnect

[ChrisWoods1977]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No error, but disconnect does not actually disconnect the spid. See Steps to Reproduce for what I've attempted.

Steps to Reproduce

In SSMS, I connect to an instance and run:
EXEC sys.sp_who @loginame = 'mylogin'

I see this connection is the only one I have. Then in powershell I run the following command:
cls $server = Connect-DbaInstance -SqlInstance ServerName $server | Disconnect-DbaInstance

I bounce back to SSMS and run the sp_who command again and I now see this new connection, even though I disconnected in the last line of the commands I ran (eventually of course I'll be running an invoke- dbaquery command between the two).

The only way the connection actually disappears is if I completely close out of powershell.

The reason I'm looking to explicitly do the connect/disconnect is I have a script that will sometimes fail in the invoke-dbaquery. And occasionally when this happens and I go to run my script again (it's setup in a way you can just rerun and it'll start off where it failed) it'll be blocked by the connection that is still open from the first time the script was ran. So then I have to go into SSMS and manually kill the spid from the first run in order to allow the subsequent run to execute. That original spid is in sleeping mode so it's not actually doing anything, but I can't make changes to the online/offline/multiuser/singleuser settings while it's still open.

I was hoping if I add a try/catch in powershell to force the connection to close, then that will fix my issue of not being able to reliably kick it off again.

Please confirm that you are running the most recent version of dbatools

Yep, just installed 2.1.11 per Slack recommendation

Other details or mentions

WSUHoey from slack assisted

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.14409.2001
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14409.2001
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU10-GDR) (KB5033592) - 16.0.4100.1 (X64) Nov 15 2023 16:11:29 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.4645.0

[wsmelton]

Overall, this is the behavior of the client library for SQL Server and/or SMO (can't recall which) since a process is tied to the connection, that process ends up being the PowerShell.exe or in your case the PowerShell_ise.exe.

The command is not really intended to close out your current connection but for "gracefully" killing other connections that are active on the instance.

Proposed Solution for the project:
We can/should probably add a warning detection if a user is trying to kill the connection associated to the current process (not sure where those details need to be pulled from but expect it is there).

Proposed Solution for your use-case:

And occasionally when this happens and I go to run my script again (it's setup in a way you can just rerun and it'll start off where it failed) it'll be blocked by the connection that is still open from the first time the script was ran.

I would recommend naming your session on each script run with a unique name that is stored maybe on local file or just have a name that is unique enough where your script as a first step can find "previously failed sessions" and kill those wherever it needs to "restart".

The other option that may work is running that workflow as a job from your script and see if that helps the "failed" runs be removed more gracefully. I can't recall if Start-Job would still close that thread out or not (been a bit since I have used it).

[andreasjordan]

You are using connection pooling, which is the default for Connect-DbaInstnce. So the disconnect only gives the connection back to the pool.

So instead of using Disconnect-DbaInstance you can use Clear-DbaConnectionPool to clear the local connection pool. You don't need any parameters. But this effects all connections, not just the one you just opened.

In your case I would use a non pooled connection by adding -NonPooledConnection to Connect-DbaInstance. In this case you can close the session with Disconnect-DbaInstance.

[andreasjordan]

Will close this now, but can reopen if needed.