functions/Invoke-DbaDbccFreeCache.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
function Invoke-DbaDbccFreeCache {
    <#
    .SYNOPSIS
        Execution of Database Console Commands that clear Server level Memory caches
 
    .DESCRIPTION
        Allows execution of Database Console Commands that act at Server Level to clear Memory caches
 
        Allows execution of the following commands
            DBCC FREEPROCCACHE
            DBCC FREESESSIONCACHE
            DBCC FREESYSTEMCACHE
 
        Read more:
            - https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql
            - https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freesessioncache-transact-sql
            - https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql
 
    .PARAMETER SqlInstance
        The target SQL Server instance or instances.
 
    .PARAMETER SqlCredential
        Login to the target instance using alternative credentials. Windows and SQL Authentication supported. Accepts credential objects (Get-Credential)
 
    .PARAMETER Operation
        DBCC Operation to Perform - Supports specific set of operations
 
    .PARAMETER InputValue
        Value used for Operation - meaning depends on Operation
        DBCC FREEPROCCACHE accepts
            a plan_handle of type varbinary(64)
            a sql_handle of type varbinary(64)
            or the name of a Resource Governor resource pool of type sysname
            If blank then clears all elements from the plan cache
        DBCC FREESYSTEMCACHE accepts
            'ALL' for ALL specifies all supported caches
            or name of a Resource Governor pool cache
        Not required for other values
 
    .PARAMETER NoInformationalMessages
        Suppresses all informational messages.
 
    .PARAMETER MarkInUseForRemoval
        Used when Operation = DBCC FREESYSTEMCACHE
        Asynchronously frees currently used entries from their respective caches after they become unused
 
    .PARAMETER WhatIf
        Shows what would happen if the cmdlet runs. The cmdlet is not run.
 
    .PARAMETER Confirm
        Prompts you for confirmation before running the cmdlet.
 
    .PARAMETER EnableException
        By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
        This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
        Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
    .NOTES
        Tags: DBCC
        Author: Patrick Flynn (@sqllensman)
 
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT
 
    .LINK
        https://dbatools.io/Invoke-DbaDbccFreeCache
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREEPROCCACHE
 
        Runs the command DBCC FREEPROCCACHE against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREESESSIONCACHE -NoInformationalMessages
 
        Runs the command DBCC FREESESSIONCACHE WITH NO_INFOMSGS against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREESYSTEMCACHE -NoInformationalMessages
 
        Runs the command DBCC FREESYSTEMCACHE WITH NO_INFOMSGS against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREEPROCCACHE -InputValue 0x060006001ECA270EC0215D05000000000000000000000000
 
        Remove a specific plan with plan_handle 0x060006001ECA270EC0215D05000000000000000000000000 from the cache via the command DBCC FREEPROCCACHE(0x060006001ECA270EC0215D05000000000000000000000000) against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREEPROCCACHE -InputValue default
 
        Runs the command DBCC FREEPROCCACHE('default') against the instance SqlServer2017 using Windows Authentication. This clears all cache entries associated with a resource pool 'default'.
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREESYSTEMCACHE -InputValue default
 
        Runs the command DBCC FREESYSTEMCACHE ('ALL', default) against the instance SqlServer2017 using Windows Authentication. This will clean all the caches with entries specific to the resource pool named "default".
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccFreeCache -SqlInstance SqlServer2017 -Operation FREESYSTEMCACHE -InputValue default -MarkInUseForRemoval
 
        Runs the command DBCC FREESYSTEMCACHE ('ALL', default) WITH MARK_IN_USE_FOR_REMOVAL against the instance SqlServer2017 using Windows Authentication. This will to release entries once the entries become unused for all the caches with entries specific to the resource pool named "default".
 
    #>

    [CmdletBinding(DefaultParameterSetName = "Default", SupportsShouldProcess, ConfirmImpact = 'High')]
    param (
        [parameter(Mandatory, ValueFromPipeline)]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [ValidateSet('FreeProcCache', 'FreeSessionCache', 'FreeSystemCache')]
        [string]$Operation = "FreeProcCache",
        [string]$InputValue,
        [switch]$NoInformationalMessages,
        [switch]$MarkInUseForRemoval,
        [switch]$EnableException
    )
    begin {

        if (Test-Bound -ParameterName Operation) {
            $Operation = $Operation.ToUpper()
        } else {
            Write-Message -Level Warning -Message "You must specify an operation "
            continue
        }

        $stringBuilder = New-Object System.Text.StringBuilder
        if ($Operation -eq 'FREESESSIONCACHE') {
            $null = $stringBuilder.Append("DBCC $Operation")
            if (Test-Bound -ParameterName NoInformationalMessages) {
                $null = $stringBuilder.Append(" WITH NO_INFOMSGS")
            }
        }
        if ($Operation -eq 'FREEPROCCACHE') {
            if (Test-Bound -ParameterName InputValue) {
                if ($InputValue.StartsWith('0x')) {
                    $null = $stringBuilder.Append("DBCC $Operation($InputValue)")
                } else {
                    $null = $stringBuilder.Append("DBCC $Operation('$InputValue')")
                }
                if (Test-Bound -ParameterName NoInformationalMessages) {
                    $null = $stringBuilder.Append(" WITH NO_INFOMSGS")
                }
            } else {
                $null = $stringBuilder.Append("DBCC $Operation")
                if (Test-Bound -ParameterName NoInformationalMessages) {
                    $null = $stringBuilder.Append(" WITH NO_INFOMSGS")
                }
            }
        }
        if ($Operation -eq 'FREESYSTEMCACHE') {
            if (Test-Bound -ParameterName InputValue) {
                $null = $stringBuilder.Append("DBCC FREESYSTEMCACHE('ALL', $InputValue)")
            } else {
                $null = $stringBuilder.Append("DBCC FREESYSTEMCACHE('ALL')")
            }
            if (Test-Bound -ParameterName NoInformationalMessages) {
                if (Test-Bound -ParameterName MarkInUseForRemoval) {
                    $null = $stringBuilder.Append(" WITH NO_INFOMSGS, MARK_IN_USE_FOR_REMOVAL")
                } else {
                    $null = $stringBuilder.Append(" WITH NO_INFOMSGS")
                }
            } elseif (Test-Bound -ParameterName MarkInUseForRemoval) {
                $null = $stringBuilder.Append(" WITH MARK_IN_USE_FOR_REMOVAL")
            }
        }
    }
    process {
        $query = $StringBuilder.ToString()

        foreach ($instance in $SqlInstance) {
            Write-Message -Message "Attempting Connection to $instance" -Level Verbose
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential
            } catch {
                Stop-Function -Message "Error occurred while establishing connection to $instance" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            try {
                if ($Pscmdlet.ShouldProcess($server.Name, "Execute the command $query against $instance")) {
                    Write-Message -Message "Query to run: $query" -Level Verbose
                    $results = $server | Invoke-DbaQuery  -Query $query -MessagesToOutput
                }
            } catch {
                Stop-Function -Message "Failure" -ErrorRecord $_ -Target $server -Continue
            }
            if ($Pscmdlet.ShouldProcess("console", "Outputting object")) {
                [PSCustomObject]@{
                    ComputerName = $server.ComputerName
                    InstanceName = $server.ServiceName
                    SqlInstance  = $server.DomainInstanceName
                    Operation    = $Operation
                    Cmd          = $query.ToString()
                    Output       = $results
                }
            }
        }
    }
}