functions/Invoke-DbaDbccDropCleanBuffer.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
function Invoke-DbaDbccDropCleanBuffer {
    <#
    .SYNOPSIS
        Execution of Database Console Command DBCC DROPCLEANBUFFERS
 
    .DESCRIPTION
        Allows execution of Database Console Command DBCC DROPCLEANBUFFERS
 
        Removes all clean buffers from the buffer pool, and columnstore objects from the columnstore object pool.
 
        Read more:
            - https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dropcleanbuffers-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 NoInformationalMessages
        Suppresses all informational messages.
 
    .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-DbaDbccDropCleanBuffer
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccDropCleanBuffer -SqlInstance SqlServer2017
 
        Runs the command DBCC DROPCLEANBUFFERS against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> Invoke-DbaDbccDropCleanBuffer -SqlInstance SqlServer2017 -NoInformationalMessages
 
        Runs the command DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS against the instance SqlServer2017 using Windows Authentication
 
    .EXAMPLE
        PS C:\> 'Sql1','Sql2/sqlexpress' | Invoke-DbaDbccDropCleanBuffer -WhatIf
 
        Displays what will happen if command DBCC DROPCLEANBUFFERS is called against Sql1 and Sql2/sqlexpress
 
    .EXAMPLE
        PS C:\> $cred = Get-Credential sqladmin
        PS C:\> Invoke-DbaDbccDropCleanBuffer -SqlInstance Server1 -SqlCredential $cred
 
        Connects using sqladmin credential and executes command DBCC DROPCLEANBUFFERS for instance Server1
 
    #>

    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High')]
    param (
        [parameter(Mandatory, ValueFromPipeline)]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [switch]$NoInformationalMessages,
        [switch]$EnableException
    )
    begin {

        $stringBuilder = New-Object System.Text.StringBuilder
        $null = $stringBuilder.Append("DBCC DROPCLEANBUFFERS")
        if (Test-Bound -ParameterName NoInformationalMessages) {
            $null = $stringBuilder.Append(" WITH NO_INFOMSGS")
        }
    }
    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 occured 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 running DBCC DROPCLEANBUFFERS" -ErrorRecord $_ -Target $server -Continue
            }
            If ($Pscmdlet.ShouldProcess("console", "Outputting object")) {
                [PSCustomObject]@{
                    ComputerName = $server.ComputerName
                    InstanceName = $server.ServiceName
                    SqlInstance  = $server.DomainInstanceName
                    Cmd          = $query.ToString()
                    Output       = $results
                }
            }
        }
    }
}