Get-DbaSuspectPage View Source Garry Bargsley (@gbargsley), blog.garrybargsley.com Windows, Linux, macOS Synopsis Retrieves suspect page records from msdb database for corruption detection and analysis
Description Queries the msdb.dbo.suspect_pages table to identify database pages that have experienced corruption events such as checksum failures, torn pages, or I/O errors. SQL Server automatically logs corrupt pages to this system table when encountered during read operations, making this function essential for proactive corruption monitoring and troubleshooting.
Get-DbaTraceFlag View Source Kevin Bullen (@sqlpadawan) Windows, Linux, macOS Synopsis Retrieves currently enabled global trace flags from SQL Server instances.
Description Queries SQL Server instances to identify which global trace flags are currently active, returning detailed status information for monitoring and compliance purposes. This is essential for auditing server configurations, troubleshooting performance issues, and ensuring trace flag consistency across environments. You can filter results to specific trace flag numbers or retrieve all enabled flags across multiple instances.
Get-DbaWaitResource View Source Stuart Moore (@napalmgram), stuart-moore.com Windows, Linux, macOS Synopsis Translates wait resource strings into human-readable database object information for troubleshooting blocking and deadlocks
Description Converts cryptic wait resource identifiers from sys.dm_exec_requests into readable database object details that DBAs can actually use for troubleshooting. When you’re investigating blocking chains or deadlocks, you see wait_resource values like ‘PAGE: 10:1:9180084’ or ‘KEY: 7:35457594073541168 (de21f92a1572)’ in DMVs, but these don’t tell you which actual table or index is involved.
Invoke-DbaDbccDropCleanBuffer View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Clears SQL Server buffer pool cache and columnstore object pool for performance testing
Description Executes DBCC DROPCLEANBUFFERS to remove all clean data pages from the buffer pool and columnstore objects from memory. This forces SQL Server to read data from disk on subsequent queries, simulating a “cold cache” environment for accurate performance testing and query optimization scenarios. DBAs use this command when they need to test query performance without the benefit of cached data pages, ensuring consistent baseline measurements across multiple test runs.
Invoke-DbaDbccFreeCache View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Clears SQL Server memory caches using DBCC commands to resolve performance issues and free memory
Description Executes DBCC commands to clear various SQL Server memory caches when troubleshooting performance problems or freeing memory on resource-constrained systems. This function helps DBAs resolve issues like parameter sniffing, plan cache bloat, or memory pressure without restarting the SQL Server service.
Supports three cache-clearing operations:
Invoke-DbaDbDbccCheckConstraint View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Validates constraint integrity by checking for constraint violations in SQL Server databases
Description Executes DBCC CHECKCONSTRAINTS to identify rows that violate CHECK, FOREIGN KEY, and other constraints in your databases. This command helps DBAs verify data integrity after bulk imports, constraint modifications, or when troubleshooting data quality issues. You can target specific tables, individual constraints, or scan entire databases for violations.
Invoke-DbaDbDbccCleanTable View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Reclaims disk space from dropped variable-length columns in tables and indexed views
Description Executes DBCC CLEANTABLE to reclaim disk space after variable-length columns (varchar, nvarchar, varbinary, text, ntext, image, xml, or CLR user-defined types) have been dropped from tables or indexed views.
When you drop variable-length columns, SQL Server doesn’t immediately reclaim the space those columns occupied. This function runs the necessary DBCC command to physically remove that unused space and compact the remaining data, which can significantly reduce table size and improve performance.
Invoke-DbaDbDbccUpdateUsage View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Corrects page and row count inaccuracies in SQL Server catalog views using DBCC UPDATEUSAGE
Description Executes the DBCC UPDATEUSAGE command to identify and fix metadata inconsistencies in catalog views that track space usage information. When these internal counters become inaccurate, system procedures like sp_spaceused return incorrect database and table size reports, making capacity planning and troubleshooting difficult.
This command is essential when you notice discrepancies between actual database file sizes and reported space usage, or when sp_spaceused shows unexpected results after bulk operations, index rebuilds, or database migrations.
Set-DbaDbIdentity View Source Patrick Flynn (@sqllensman) Windows, Linux, macOS Synopsis Checks and resets identity column values using DBCC CHECKIDENT
Description Executes DBCC CHECKIDENT to verify the current identity value for tables with identity columns and optionally reseed them to a specific value.
This is essential after bulk data operations, imports, or deletes that can leave identity values out of sync with actual table data.
When run without ReSeedValue, it reports the current identity value and the maximum value in the identity column.
dbatools