Disconnect-DbaInstance does not actually disconnect

[ChrisWoods1977]

Disconnect-DbaInstance does not actually disconnect

[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.