Get-DbaDbPageInfo

Synopsis

Retrieves detailed page allocation information from SQL Server databases for storage analysis and troubleshooting

Description

This function queries the sys.dm_db_database_page_allocations dynamic management view to return detailed information about page allocation, including page type, free space percentage, allocation status, and mixed page allocation indicators.
Use this when troubleshooting storage issues, analyzing space utilization patterns, or investigating page-level performance problems in your databases.
Results can be filtered by specific databases, schemas, and tables to focus your analysis on problem areas.
Requires SQL Server 2012 or higher as it depends on the sys.dm_db_database_page_allocations DMV.

Syntax

Get-DbaDbPageInfo
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-Schema] <String[]>]
    [[-Table] <String[]>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbPageInfo -SqlInstance sql2017

Returns page information for all databases on sql2017

Example: 2

PS C:\> Get-DbaDbPageInfo -SqlInstance sql2017, sql2016 -Database testdb

Returns page information for the testdb on sql2017 and sql2016

Example: 3

PS C:\> $servers | Get-DbaDatabase -Database testdb | Get-DbaDbPageInfo

Returns page information for the testdb on all $servers

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 page allocation information. Accepts wildcards for pattern matching.
Use this when you need to focus on specific databases rather than scanning all databases on the instance.

-Schema

Limits the analysis to tables within specific schemas only. Multiple schema names can be provided.
Helpful when troubleshooting page issues in specific application schemas or when you want to exclude system schemas from results.

-Table

Restricts page information retrieval to specific tables only. Can be combined with Schema parameter for precise targeting.
Use this when investigating page allocation problems for known problematic tables or when performing focused storage analysis.

-InputObject

Accepts database objects piped from Get-DbaDatabase, allowing you to chain commands together.
This enables scenarios like getting databases from multiple instances and then analyzing their page information in a single pipeline.

-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

System.Data.DataRow

Returns one object per page allocation record from the sys.dm_db_database_page_allocations dynamic management view. Each row contains detailed page allocation information for tables in the specified databases.

Properties:

• ComputerName: The computer name of the SQL Server instance
• InstanceName: The SQL Server instance name
• SqlInstance: The full SQL Server instance name
• Database: The name of the database containing the table
• Schema: The schema name containing the table
• Table: The table name
• PageType: Type of the page (e.g., ‘DATA_PAGE’, ‘INDEX_PAGE’, ‘LOB_DATA_PAGE’)
• PageFreePercent: Percentage of free space available on the page (0-100)
• IsAllocated: String value (‘True’ or ‘False’) indicating if the page is allocated
• IsMixedPage: String value (‘True’ or ‘False’) indicating if this is a mixed page allocation