functions/Get-DbaDbLogSpace.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
Function Get-DbaDbLogSpace {
    <#
    .SYNOPSIS
        Gets information on the database transaction log usage for each instance(s) of SQL Server.
 
    .DESCRIPTION
        Returns transaction log size and space used for each database on the SQL Server instance(s).
 
        This can be used to monitor how much of your allocated transaction log space is in use, and whether it is nearing the point where it will need to grow.
 
    .PARAMETER SqlInstance
        SQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.
 
    .PARAMETER SqlCredential
        Login to the target instance using alternate Windows or SQL Login Authentication. Accepts credential objects (Get-Credential).
 
    .PARAMETER Database
        The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will be processed.
 
    .PARAMETER ExcludeDatabase
        The database(s) to exclude - this list is auto-populated from the server.
 
    .PARAMETER ExcludeSystemDatabase
        Allows you to suppress output on system databases.
 
    .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: Database, LogFile
        Author: Jess Pomfret, JessPomfret.com
 
        Website: https://dbatools.io
        Copyright: (c) 2019 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT
 
    .LINK
        https://dbatools.io/Get-DbaDbLogSpace
 
    .EXAMPLE
        PS C:\> Get-DbaDbLogSpace -SqlInstance Server1
 
        Returns the transaction log usage information for all databases on Server1
 
    .EXAMPLE
        PS C:\> Get-DbaDbLogSpace -SqlInstance Server1 -Database Database1, Database2
 
        Returns the transaction log usage information for both Database1 and Database 2 on Server1
 
    .EXAMPLE
        PS C:\> Get-DbaDbLogSpace -SqlInstance Server1 -ExcludeDatabase Database3
 
        Returns the transaction log usage information for all databases on Server1, except Database3
 
    .EXAMPLE
        PS C:\> Get-DbaDbLogSpace -SqlInstance Server1 -ExcludeSystemDatabase
 
        Returns the transaction log usage information for all databases on Server1, except the system databases
 
    .EXAMPLE
        PS C:\> Get-DbaRegisteredServer -SqlInstance cmsServer | Get-DbaDbLogSpace -Database Database1
 
        Returns the transaction log usage information for Database1 for a group of servers from SQL Server Central Management Server (CMS).
 
    #>

    [CmdletBinding()]
    param (
        [parameter(ValueFromPipeline, Mandatory)]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [string[]]$Database,
        [string[]]$ExcludeDatabase,
        [switch]$ExcludeSystemDatabase,
        [switch]$EnableException
    )

    process {
        foreach ($instance in $SqlInstance) {
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential
            } catch {
                Stop-Function -Message "Failed to process Instance $Instance" -ErrorRecord $_ -Target $instance -Continue
            }
            $dbs = $server.Databases | Where-Object IsAccessible

            if ($Database) {
                $dbs = $dbs | Where-Object Name -in $Database
            }
            if ($ExcludeDatabase) {
                $dbs = $dbs | Where-Object Name -NotIn $ExcludeDatabase
            }

            if ($ExcludeSystemDatabase) {
                $dbs = $dbs | Where-Object IsSystemObject -eq $false
            }

            # 2012+ use new DMV
            if ($server.versionMajor -ge 11) {
                foreach ($db in $dbs) {
                    try {
                        $logspace = $server.query('select * from sys.dm_db_log_space_usage', $db.name)
                    } catch {
                        Stop-Function -Message "Unable to collect log space data on $instance." -ErrorRecord $_ -Target $db -Continue
                    }
                    [pscustomobject]@{
                        ComputerName        = $server.ComputerName
                        InstanceName        = $server.ServiceName
                        SqlInstance         = $server.DomainInstanceName
                        Database            = $db.name
                        LogSize             = [dbasize]($logspace.total_log_size_in_bytes)
                        LogSpaceUsedPercent = $logspace.used_log_space_in_percent
                        LogSpaceUsed        = [dbasize]($logspace.used_log_space_in_bytes)
                    }
                }
            } else {
                try {
                    $logspace = $server.Query("dbcc sqlperf(logspace)") | Where-Object { $dbs.name -contains $_.'Database Name' }
                } catch {
                    Stop-Function -Message "Unable to collect log space data on $instance." -ErrorRecord $_ -Target $db -Continue
                }

                foreach ($ls in $logspace) {
                    [pscustomobject]@{
                        ComputerName        = $server.ComputerName
                        InstanceName        = $server.ServiceName
                        SqlInstance         = $server.DomainInstanceName
                        Database            = $ls.'Database Name'
                        LogSize             = [dbasize]($ls.'Log Size (MB)' * 1MB)
                        LogSpaceUsedPercent = $ls.'Log Space Used (%)'
                        LogSpaceUsed        = [dbasize]($ls.'Log Size (MB)' * ($ls.'Log Space Used (%)' / 100) * 1MB)
                    }
                }
            }
        }
    }
}