functions/Get-DbaDbFile.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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Get-DbaDbFile {
    <#
    .SYNOPSIS
        Returns detailed information about database files.
 
    .DESCRIPTION
        Returns detailed information about database files. Does not use SMO - SMO causes enumeration and this command avoids that.
 
    .PARAMETER SqlInstance
        The target SQL Server instance or instances
 
    .PARAMETER SqlCredential
        Credentials to connect to the SQL Server instance if the calling user doesn't have permission
 
    .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 InputObject
        A piped collection of database objects
 
    .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
        Author: Stuart Moore (@napalmgram), stuart-moore.com
 
        Website: https://dbatools.io
        Copyright: (c) 2018 by dbatools, licensed under MIT
        License: MIT https://opensource.org/licenses/MIT
 
    .EXAMPLE
        PS C:\> Get-DbaDbFile -SqlInstance sql2016
 
        Will return an object containing all file groups and their contained files for every database on the sql2016 SQL Server instance
 
    .EXAMPLE
        PS C:\> Get-DbaDbFile -SqlInstance sql2016 -Database Impromptu
 
        Will return an object containing all file groups and their contained files for the Impromptu Database on the sql2016 SQL Server instance
 
    .EXAMPLE
        PS C:\> Get-DbaDbFile -SqlInstance sql2016 -Database Impromptu, Trading
 
        Will return an object containing all file groups and their contained files for the Impromptu and Trading databases on the sql2016 SQL Server instance
 
#>

    [CmdletBinding(DefaultParameterSetName = "Default")]
    param (
        [parameter(ParameterSetName = "Pipe", Mandatory, ValueFromPipeline)]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [Alias("Databases")]
        [object[]]$Database,
        [object[]]$ExcludeDatabase,
        [object[]]$InputObject,
        [Alias('Silent')]
        [switch]$EnableException
    )

    process {

        foreach ($instance in $sqlInstance) {
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $sqlcredential
            } catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            $sql = "select
            fg.name as FileGroupName,
            df.file_id as 'ID',
            df.Type,
            df.type_desc as TypeDescription,
            df.name as LogicalName,
            mf.physical_name as PhysicalName,
            df.state_desc as State,
            df.max_size as MaxSize,
            case mf.is_percent_growth when 1 then df.growth else df.Growth*8 end as Growth,
            fileproperty(df.name, 'spaceused') as UsedSpace,
            df.size as Size,
            vfs.size_on_disk_bytes as size_on_disk_bytes,
            case df.state_desc when 'OFFLINE' then 'True' else 'False' End as IsOffline,
            case mf.is_read_only when 1 then 'True' when 0 then 'False' End as IsReadOnly,
            case mf.is_media_read_only when 1 then 'True' when 0 then 'False' End as IsReadOnlyMedia,
            case mf.is_sparse when 1 then 'True' when 0 then 'False' End as IsSparse,
            case mf.is_percent_growth when 1 then 'Percent' when 0 then 'kb' End as GrowthType,
            case mf.is_read_only when 1 then 'True' when 0 then 'False' End as IsReadOnly,
            vfs.num_of_writes as NumberOfDiskWrites,
            vfs.num_of_reads as NumberOfDiskReads,
            vfs.num_of_bytes_read as BytesReadFromDisk,
            vfs.num_of_bytes_written as BytesWrittenToDisk,
            fg.data_space_id as FileGroupDataSpaceId,
            fg.Type as FileGroupType,
            fg.type_desc as FileGroupTypeDescription,
            case fg.is_default When 1 then 'True' when 0 then 'False' end as FileGroupDefault,
            fg.is_read_only as FileGroupReadOnly"


            $sqlfrom = "from sys.database_files df
            left outer join sys.filegroups fg on df.data_space_id=fg.data_space_id
            inner join sys.dm_io_virtual_file_stats(db_id(),NULL) vfs on df.file_id=vfs.file_id
            inner join sys.master_files mf on df.file_id = mf.file_id
            and mf.database_id = db_id()"


            $sql2008 = ",vs.available_bytes as 'VolumeFreeSpace'"
            $sql2008from = "cross apply sys.dm_os_volume_stats(db_id(),df.file_id) vs"

            $sql2000 = "select
            fg.groupname as FileGroupName,
            df.fileid as ID,
            CONVERT(INT,df.status & 0x40) / 64 as Type,
            case CONVERT(INT,df.status & 0x40) / 64 when 1 then 'LOG' else 'ROWS' end as TypeDescription,
            df.name as LogicalName,
            df.filename as PhysicalName,
            'Existing' as State,
            df.maxsize as MaxSize,
            case CONVERT(INT,df.status & 0x100000) / 1048576 when 1 then df.growth when 0 then df.growth*8 End as Growth,
            fileproperty(df.name, 'spaceused') as UsedSpace,
            df.size as Size,
            case CONVERT(INT,df.status & 0x20000000) / 536870912 when 1 then 'True' else 'False' End as IsOffline,
            case CONVERT(INT,df.status & 0x10) / 16 when 1 then 'True' when 0 then 'False' End as IsReadOnly,
            case CONVERT(INT,df.status & 0x1000) / 4096 when 1 then 'True' when 0 then 'False' End as IsReadOnlyMedia,
            case CONVERT(INT,df.status & 0x10000000) / 268435456 when 1 then 'True' when 0 then 'False' End as IsSparse,
            case CONVERT(INT,df.status & 0x100000) / 1048576 when 1 then 'Percent' when 0 then 'kb' End as GrowthType,
            case CONVERT(INT,df.status & 0x1000) / 4096 when 1 then 'True' when 0 then 'False' End as IsReadOnly,
            fg.groupid as FileGroupDataSpaceId,
            NULL as FileGroupType,
            NULL AS FileGroupTypeDescription,
            CAST(fg.Status & 0x10 as BIT) as FileGroupDefault,
            CAST(fg.Status & 0x8 as BIT) as FileGroupReadOnly
            from sysfiles df
            left outer join sysfilegroups fg on df.groupid=fg.groupid"


            if ($Database) {
                $InputObject = $server.Databases | Where-Object Name -in $database
            } else {
                $InputObject = $server.Databases
            }

            if ($ExcludeDatabase) {
                $InputObject = $InputObject | Where-Object Name -NotIn $ExcludeDatabase
            }

            foreach ($db in $InputObject) {
                if (!$db.IsAccessible) {
                    Write-Message -Level Warning -Message "Database $db is not accessible. Skipping"
                    continue
                }
                Write-Message -Level Verbose -Message "Querying database $db"

                $version = Test-DbaDbCompatibility -SqlInstance $server -Database $db.Name | Select-Object DatabaseCompatibility
                $version = + ($version.DatabaseCompatibility.ToString().replace("Version", "")) / 10

                if ($version -ge 11) {
                    $query = ($sql, $sql2008, $sqlfrom, $sql2008from) -Join "`n"
                } elseif ($version -ge 9) {
                    $query = ($sql, $sqlfrom) -Join "`n"
                } else {
                    $query = $sql2000
                }

                Write-Message -Level Debug -Message "SQL Statement: $query"

                $results = $server.Query($query, $db.Name)

                foreach ($result in $results) {
                    $size = [dbasize]($result.Size * 8192)
                    $usedspace = [dbasize]($result.UsedSpace * 8192)
                    $maxsize = $result.MaxSize
                    # calculation is done here because for snapshots or sparse files size is not the "virtual" size
                    # (master_files.Size) but the currently allocated one (dm_io_virtual_file_stats.size_on_disk_bytes)
                    $AvailableSpace = $size - $usedspace
                    if ($result.size_on_disk_bytes) {
                        $size = [dbasize]($result.size_on_disk_bytes)
                    }
                    if ($maxsize -gt -1) {
                        $maxsize = [dbasize]($result.MaxSize * 8192)
                    } else {
                        $maxsize = [dbasize]($result.MaxSize)
                    }

                    if ($result.VolumeFreeSpace) {
                        $VolumeFreeSpace = [dbasize]$result.VolumeFreeSpace
                    } else {
                        # to get drive free space for each drive that a database has files on
                        # when database compatibility lower than 110. Lets do this with query2
                        $query2 = @'
-- to get drive free space for each drive that a database has files on
DECLARE @FixedDrives TABLE(Drive CHAR(1), MB_Free BIGINT);
INSERT @FixedDrives EXEC sys.xp_fixeddrives;
 
SELECT DISTINCT fd.MB_Free, LEFT(df.physical_name, 1) AS [Drive]
FROM @FixedDrives AS fd
INNER JOIN sys.database_files AS df
ON fd.Drive = LEFT(df.physical_name, 1);
'@

                        # if the server has one drive xp_fixeddrives returns one row, but we still need $disks to be an array.
                        if ($server.VersionMajor -gt 8) {
                            $disks = @($server.Query($query2, $db.Name))
                            $MbFreeColName = $disks[0].psobject.Properties.Name
                            # get the free MB value for the drive in question
                            $free = $disks | Where-Object {
                                $_.drive -eq $result.PhysicalName.Substring(0, 1)
                            } | Select-Object $MbFreeColName
                            
                            $VolumeFreeSpace = [dbasize](($free.MB_Free) * 1024 * 1024)
                        }
                    }
                    if ($result.GrowthType -eq "Percent") {
                        $nextgrowtheventadd = [dbasize]($result.size * ($result.Growth * 0.01) * 1024)
                    } else {
                        $nextgrowtheventadd = [dbasize]($result.Growth * 8 * 1024)
                    }
                    if ( ($nextgrowtheventadd.Byte -gt ($MaxSize.Byte - $size.Byte)) -and $maxsize -gt 0 ) { [dbasize]$nextgrowtheventadd = 0 }

                    [PSCustomObject]@{
                        ComputerName             = $server.ComputerName
                        InstanceName             = $server.ServiceName
                        SqlInstance              = $server.DomainInstanceName
                        Database                 = $db.name
                        FileGroupName            = $result.FileGroupName
                        ID                       = $result.ID
                        Type                     = $result.Type
                        TypeDescription          = $result.TypeDescription
                        LogicalName              = $result.LogicalName.Trim()
                        PhysicalName             = $result.PhysicalName.Trim()
                        State                    = $result.State
                        MaxSize                  = $maxsize
                        Growth                   = $result.Growth
                        GrowthType               = $result.GrowthType
                        NextGrowthEventSize      = $nextgrowtheventadd
                        Size                     = $size
                        UsedSpace                = $usedspace
                        AvailableSpace           = $AvailableSpace
                        IsOffline                = $result.IsOffline
                        IsReadOnly               = $result.IsReadOnly
                        IsReadOnlyMedia          = $result.IsReadOnlyMedia
                        IsSparse                 = $result.IsSparse
                        NumberOfDiskWrites       = $result.NumberOfDiskWrites
                        NumberOfDiskReads        = $result.NumberOfDiskReads
                        ReadFromDisk             = [dbasize]$result.BytesReadFromDisk
                        WrittenToDisk            = [dbasize]$result.BytesWrittenToDisk
                        VolumeFreeSpace          = $VolumeFreeSpace
                        FileGroupDataSpaceId     = $result.FileGroupDataSpaceId
                        FileGroupType            = $result.FileGroupType
                        FileGroupTypeDescription = $result.FileGroupTypeDescription
                        FileGroupDefault         = $result.FileGroupDefault
                        FileGroupReadOnly        = $result.FileGroupReadOnly
                    }
                }
            }
        }
    }
    end {
        Test-DbaDeprecation -DeprecatedOn "1.0.0" -EnableException:$false -Alias Get-DbaDatabaseFIle
    }
}