microsoft / microsoft/sqlmanagementobjects

Intermittent errors with SMO expanding databases when they're in an AG

Open
#201 0 comments 3 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
143
Forks
28
PR merge metrics
No merged PRs in 30d

Description

So this is hard to explain, there is a bug report for dbatools that I am also experiencing, where running Copy-DbaLogin works for most users most of the time, but pretty much every day I get at least one (different login, different database in error message) that fails out with the following error:

The target database ('DBNAME') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

The normal path is that these databases are just skipped, but occasionally it seems like SMO tries to get in for some reason.

This is the get-error output

Exception             : 
    Type                        : System.Management.Automation.MethodInvocationException
    ErrorRecord                 : 
        Exception             : 
            Type    : System.Management.Automation.ParentContainsErrorRecordException
            Message : Exception calling "EnumProcesses" with "0" argument(s): "Enumerate processes failed for Server 'SERVERNAME'. "
            HResult : -2146233087
        CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
        FullyQualifiedErrorId : FailedOperationException
        InvocationInfo        : 
            ScriptLineNumber : 324
            OffsetInLine     : 33
            HistoryId        : 81
            ScriptName       : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
            Line             : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
                               
            Statement        : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
            PositionMessage  : At C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1:324 char:33
                               +              $activeConnections = $destServer.EnumProcesses() | Where- 
                               +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            PSScriptRoot     : C:\temp\loginrefresh\dbatools\dbatools\public
            PSCommandPath    : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
            CommandOrigin    : Internal
        ScriptStackTrace      : at Copy-Login, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 324
                                at Copy-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 462
                                at Select-DefaultView<Process>, C:\temp\loginrefresh\dbatools\dbatools\private\functions\Select-DefaultView.ps1: line 70
                                at Get-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Get-DbaLogin.ps1: line 281
                                at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 24
                                at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 22
                                at <ScriptBlock>, <No file>: line 1
    WasThrownFromThrowStatement : True
    TargetSite                  : 
        Name          : CheckActionPreference
        DeclaringType : [System.Management.Automation.ExceptionHandlingOps]
        MemberType    : Method
        Module        : System.Management.Automation.dll
    Message                     : Exception calling "EnumProcesses" with "0" argument(s): "Enumerate processes failed for Server 'SERVERNAME'. "
    Data                        : System.Collections.ListDictionaryInternal
    InnerException              : 
        Type             : Microsoft.SqlServer.Management.Smo.FailedOperationException
        SmoExceptionType : FailedOperationException
        Operation        : Enumerate processes
        FailedObject     : [SERVERNAME]
        Message          : Enumerate processes failed for Server 'SERVERNAME'. 
        HelpLink         : https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.76.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+processes+Server&LinkId=20476
        TargetSite       : 
            Name          : EnumProcesses
            DeclaringType : [Microsoft.SqlServer.Management.Smo.Server]
            MemberType    : Method
            Module        : Microsoft.SqlServer.Smo.dll
        Data             : System.Collections.ListDictionaryInternal
        InnerException   : 
            Type           : Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException
            TargetSite     : 
                Name          : Process
                DeclaringType : [Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator]
                MemberType    : Method
                Module        : Microsoft.SqlServer.Management.Sdk.Sfc.dll
            Message        : Failed to retrieve data for this request.
            Data           : System.Collections.ListDictionaryInternal
            InnerException : 
                Type               : Microsoft.Data.SqlClient.SqlException
                Errors             : 
                    Source     : Core Microsoft SqlClient Data Provider
                    Number     : 978
                    State      : 1
                    Class      : 14
                    Server     : SERVERNAME
                    Message    : The target database ('DBNAME') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
                    LineNumber : 1
                ClientConnectionId : 48273446-1530-408d-9685-b3f0e9eb411b
                Class              : 14
                LineNumber         : 1
                Number             : 978
                Server             : SERVERNAME
                State              : 1
                Source             : Core Microsoft SqlClient Data Provider
                ErrorCode          : -2146232060
                TargetSite         : 
                    Name          : ThrowExceptionAndWarning
                    DeclaringType : [Microsoft.Data.SqlClient.TdsParser]
                    MemberType    : Method
                    Module        : Microsoft.Data.SqlClient.dll
                Message            : The target database ('DBNAME') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
                Data               : System.Collections.ListDictionaryInternal
                HResult            : -2146232060
                StackTrace         : 
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.SqlServer.Management.Smo.DataProvider.ReadInternal()
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
            Source         : Microsoft.SqlServer.Management.Sdk.Sfc
            HResult        : -2146233088
            StackTrace     : 
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorData(Request req)
   at Microsoft.SqlServer.Management.Smo.Server.EnumProcesses()
        Source           : Microsoft.SqlServer.Smo
        HResult          : -2146233088
        StackTrace       : 
   at Microsoft.SqlServer.Management.Smo.Server.EnumProcesses()
   at CallSite.Target(Closure, CallSite, Object)
    Source                      : System.Management.Automation
    HResult                     : -2146233087
    StackTrace                  : 
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
InvocationInfo        : 
    ScriptLineNumber : 324
    OffsetInLine     : 33
    HistoryId        : 81
    ScriptName       : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
    Line             : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
                       
    Statement        : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
    PositionMessage  : At C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1:324 char:33
                       +              $activeConnections = $destServer.EnumProcesses() | Where- 
                       +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PSScriptRoot     : C:\temp\loginrefresh\dbatools\dbatools\public
    PSCommandPath    : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
    CommandOrigin    : Internal
ScriptStackTrace      : at Copy-Login, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 324
                        at Copy-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 462
                        at Select-DefaultView<Process>, C:\temp\loginrefresh\dbatools\dbatools\private\functions\Select-DefaultView.ps1: line 70
                        at Get-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Get-DbaLogin.ps1: line 281
                        at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 24
                        at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 22
                        at <ScriptBlock>, <No file>: line 1
                        ```

these are the commands that seem to throw the errors most often, but there is more info on the dbatools issue.

$destServer = Connect-DbaInstance -SqlInstance mssql1
$activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
#or
$ownedDbs = $destServer.Databases | Where-Object Owner -eq $newUserName

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reading the linked dbatools report and tracing the reported entry points: EnumProcesses() and the Databases collection, with the caller shown at Copy-DbaLogin.ps1 line 324. Reproduce the availability-group error and compare both paths; done means the intermittent failure is understood and verified against the affected SMO behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, powershell, sql
Domain
backend-api-design, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.