Get-DbaDbFile

Synopsis

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

Description

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.

Syntax

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

Examples

Example: 1

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

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

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

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

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

Return any files that are in the Index filegroup of the AdventureWorks2017 database.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances

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

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

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

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

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

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

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.