Unable to disconnect from server

Dec 22, 2011 at 1:57 PM

I noticed recently that connections are not being removed until I completely exit out of Powershell (or PowerGUI). My guess is that connection pooling is being used(?), so it's keeping a connection open to the server the whole time. I tried to manually disconnect, but although I don't get an error the connection is still there as well:

$server = Get-SqlServer $serverName

$server.ConnectionContext.Disconnect()

I tried changing the SQLServer module to make sure that the connection was non-pooled, but even when I did that the connection still refused to close. Again, no errors, but the connection remained open.

Our scripts typically run and close, which removes the connection so this isn't a big deal for us at the moment. However, when I try to run jobs asynchronously so that we can have parallel processing using Start-Job, the job never gets marked as completed. This only happens when the job contains SQL code so I'm concerned that the open connection is causing the job to hang.

Thanks for any guidance that you can give.

Coordinator
Dec 23, 2011 at 12:30 AM

This is an SMO thing. Take a look at this related post:

http://sqlpsx.codeplex.com/discussions/211590

There's also mention of the connection issue in this blog post and a suggested workaround for first creating a single database object:

http://blogs.technet.com/b/heyscriptingguy/archive/2011/06/01/use-powershell-to-search-sql-server-logs-for-errors.aspx