Get-DbaDbFile

Stuart Moore (@napalmgram), stuart-moore.com

Windows, Linux, macOS

Synopsis

Retrieves comprehensive database file information including size, growth, I/O statistics, and storage details.

Retrieves detailed information about database files (data and log files) from SQL Server instances using direct T-SQL queries for optimal performance. This function provides comprehensive file metadata including current size, used space, growth settings, I/O statistics, and volume free space information that DBAs need for capacity planning, performance analysis, and storage management. Unlike SMO-based approaches, this command avoids costly enumeration operations and provides faster results when analyzing file configurations across multiple databases.

Databases that cannot be opened - RESTORING, RECOVERY_PENDING, SUSPECT or OFFLINE - are read from sys.master_files in master instead of from inside the database, so a database left in NORECOVERY between a full and a differential restore still reports its logical and physical file names. Only the configured file layout is authoritative there: used space, available space, filegroup metadata, the I/O counters and volume free space are returned as null for those databases, and reading them needs CREATE DATABASE, ALTER ANY DATABASE or VIEW ANY DEFINITION permission on the instance.

Syntax

Get-DbaDbFile
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-FileGroup] <Object[]>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example 1: Will return an object containing all file groups and their contained files for every database on the sql2016...

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 2: Will return an object containing all file groups and their contained files for the Impromptu Database on the...

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 3: Will return an object containing all file groups and their contained files for the Impromptu and Trading...

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

Example 4: Will accept piped input from Get-DbaDatabase and return an object containing all file groups and their...

PS C:\> Get-DbaDatabase -SqlInstance sql2016 -Database Impromptu, Trading | Get-DbaDbFile

Will accept piped input from Get-DbaDatabase and return an object containing all file groups and their contained files for the Impromptu and Trading databases on the sql2016 SQL Server instance

Example 5: Return any files that are in the Index filegroup of the AdventureWorks2017 database

PS C:\> Get-DbaDbFile -SqlInstance sql2016 -Database AdventureWorks2017 -FileGroup Index

Example 6: Returns the logical and physical file names of Staging even while it sits in NORECOVERY between a full and a...

PS C:\> Get-DbaDbFile -SqlInstance sql2016 -Database Staging | Select-Object LogicalName, PhysicalName

Returns the logical and physical file names of Staging even while it sits in NORECOVERY between a full and a differential restore, by reading them from sys.master_files.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances

Property	Value
Alias
Required	False
Pipeline	true (ByValue)
Default Value

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

Property	Value
Alias
Required	False
Pipeline	false
Default Value

-Database

Specifies which databases to analyze for file information. Accepts wildcards for pattern matching.
Use this when you need to focus on specific databases rather than scanning all databases on the instance.
Particularly useful for capacity planning or troubleshooting file growth issues on targeted databases.

Property	Value
Alias
Required	False
Pipeline	false
Default Value

-ExcludeDatabase

Excludes specific databases from the file analysis. Accepts wildcards for pattern matching.
Use this to skip system databases, test databases, or databases you don’t need to analyze.
Helpful when performing routine file space reviews while avoiding databases that don’t require monitoring.

Property	Value
Alias
Required	False
Pipeline	false
Default Value

-FileGroup

Filters results to show only files within the specified filegroup name.
Use this when analyzing specific filegroups for space utilization, I/O patterns, or growth planning.
Particularly valuable when troubleshooting performance issues or planning filegroup-specific storage migrations.

Property	Value
Alias
Required	False
Pipeline	false
Default Value

-InputObject

Accepts database objects piped from other dbatools commands like Get-DbaDatabase.
Use this for advanced filtering scenarios or when chaining multiple dbatools commands together.
Allows you to pre-filter databases using complex criteria before analyzing their file information.

Property	Value
Alias
Required	False
Pipeline	true (ByValue)
Default Value

-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.

Property	Value
Alias
Required	False
Pipeline	false
Default Value	False

Outputs

PSCustomObject

Returns one object per database file on the SQL Server instance(s).

Properties:

ComputerName: The computer name of the SQL Server instance
InstanceName: The SQL Server instance name
SqlInstance: The full SQL Server instance name (computer\instance)
Database: Name of the database containing the file
DatabaseID: Internal ID of the database
FileGroupName: Name of the filegroup containing this file (NULL for log files)
ID: File ID within the database
Type: Type of file - 0 for data file, 1 for log file (Integer)
TypeDescription: Human-readable file type (ROWS or LOG)
LogicalName: Logical name of the file within SQL Server
PhysicalName: Operating system file path
State: Current state of the file (ONLINE, OFFLINE, etc.)
MaxSize: Maximum size the file can grow to - displays as dbasize object (KB, MB, GB, etc.)
Growth: Growth increment - value depends on GrowthType
GrowthType: How the file grows (Percent or KB)
NextGrowthEventSize: Size added during next autogrow event - displays as dbasize object
Size: Current size of the file - displays as dbasize object
UsedSpace: Space currently used within the file - displays as dbasize object
AvailableSpace: Free space within the file (Size - UsedSpace) - displays as dbasize object
IsOffline: Boolean indicating if the file is offline
IsReadOnly: Boolean indicating if the file is read-only
IsReadOnlyMedia: Boolean indicating if the underlying storage media is read-only
IsSparse: Boolean indicating if the file is sparse (snapshots)
NumberOfDiskWrites: Count of write operations to the file since instance startup
NumberOfDiskReads: Count of read operations from the file since instance startup
ReadFromDisk: Total bytes read from the file since instance startup - displays as dbasize object
WrittenToDisk: Total bytes written to the file since instance startup - displays as dbasize object
VolumeFreeSpace: Free space available on the volume containing this file - displays as dbasize object
FileGroupDataSpaceId: Internal ID of the filegroup data space
FileGroupType: Type of filegroup (NULL for log files, or name for data filegroups)
FileGroupTypeDescription: Description of filegroup type
FileGroupDefault: Boolean indicating if this is the default filegroup
FileGroupReadOnly: Boolean indicating if the filegroup is read-only Note: Size-related properties (Size, UsedSpace, MaxSize, etc.) are returned as dbasize objects which automatically format as human-readable units (KB, MB, GB, TB) when displayed. Note: For a database that cannot be opened the file layout is read from sys.master_files, and FileGroupName, UsedSpace, AvailableSpace, NumberOfDiskWrites, NumberOfDiskReads, ReadFromDisk, WrittenToDisk, VolumeFreeSpace, FileGroupType, FileGroupTypeDescription, FileGroupDefault and FileGroupReadOnly are NULL because they have no authoritative value outside the database. Note: State and IsOffline describe the file, not the database. sys.master_files reports the files of a database that was taken offline as ONLINE, so a database in that state is recognised by its own status and not by these two properties. A database left in NORECOVERY does report its files as RESTORING. Note: For a database that cannot be opened, Size is the size recorded in sys.master_files. For an accessible database Size is instead taken from sys.dm_io_virtual_file_stats where that is available, so the two differ for sparse files, database snapshots and FILESTREAM containers, where the recorded size is not the space allocated on disk.