debugsqlMinor
"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell
Viewed 0 times
numberthemaximumerrorlogconnectingmessagepowershelldacdedicated
Problem
I'm connecting to SQL Server (2016 and 2017 latest builds) via the Dedicated Administrator Connection (DAC) from PowerShell.
The following error message is recorded in the SQL Server Error Log:
Date 4/2/2019 1:59:13 PM
Log SQL Server (Current - 4/2/2019 1:59:00 PM)
Source Logon
Message
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
The query runs successfully. Numerous connection string management iterations have been attempted; this one is the most robust so far.
A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server Error Log, so no joy there.
Examining
This error is just noise. The DAC query runs and works as expected.
The error is recorded every time even with a fresh restart on a quiesced system. There are NO other DAC connections in use - if there was, Powershell would throw an obvious error message at the command prompt.
Interestingly, when using
```
#begin powershell script
$SqlServerName = "server\instance"
$DbQuery = "
INSERT INTO master.dbo.sometable(value1,value2) values ('test14','testtest14');"
function Get-SqlConnection
{
param (
[String] $SqlServerName
)
$sqlConnection = $null
try
{
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.Conn
The following error message is recorded in the SQL Server Error Log:
Date 4/2/2019 1:59:13 PM
Log SQL Server (Current - 4/2/2019 1:59:00 PM)
Source Logon
Message
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
The query runs successfully. Numerous connection string management iterations have been attempted; this one is the most robust so far.
A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server Error Log, so no joy there.
Examining
sys.dm_exec_sessions reveals nothing of interest; no connections remain open using this technique. The below PowerShell has a dummy query in it, I can't talk about why we are connecting this way because it is proprietary, but it is 100% necessary, it is a very quick connection, and I need to do it once every 10 minutes.This error is just noise. The DAC query runs and works as expected.
The error is recorded every time even with a fresh restart on a quiesced system. There are NO other DAC connections in use - if there was, Powershell would throw an obvious error message at the command prompt.
Interestingly, when using
sqlcmd, no error message is recorded in the SQL Server Error Log.```
#begin powershell script
$SqlServerName = "server\instance"
$DbQuery = "
INSERT INTO master.dbo.sometable(value1,value2) values ('test14','testtest14');"
function Get-SqlConnection
{
param (
[String] $SqlServerName
)
$sqlConnection = $null
try
{
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.Conn
Solution
After working up through several layers of Microsoft support, it was discovered that the solution to this issue was to change
pooling=false to Pooling=False - apparently keywords and values are case sensitive.Context
StackExchange Database Administrators Q#233777, answer score: 2
Revisions (0)
No revisions yet.