Azure SQL BACPAC Export Failure with CDC & db_cdcreader (SQL71501)
Contributor: hudajazmawi vnmsftit Overview Exporting an Azure SQL Database to a BACPAC using SqlPackage / SSMS may fail when Change Data Capture (CDC) is enabled and database users (or Entra groups) are assigned to CDC-related roles such as db_cdcreader. A common error observed: Error SQL71501: Error validating element: Role Membership: has an unresolved reference to Role [db_cdcreader]. This issue can be confusing because: The database is healthy CDC is functioning correctly The error occurs only during export Scenario From a real customer case: Azure SQL Database with CDC enabled An Entra (AAD) group added to db_cdcreader Export attempted via SqlPackage (v170+) Export fails during schema validation phase Root Cause Explained 1. SqlPackage performs strict schema modeling During export, SqlPackage (via DacFx) builds a logical schema model of the database. Every object must be fully resolvable Roles and role memberships are validated Any missing/unsupported object → export fails This is why the error appears as: SQL71501 – unresolved reference 2. CDC introduces system-managed objects When CDC is enabled, SQL automatically creates: cdc schema System tables Special roles: db_cdcreader cdc_admin These objects are not treated as regular user-defined objects: They are system-managed Some are implicitly created Some are not fully modeled/exported by DacFx 3. Role membership is the breaking point The failure does not happen because the role exists It happens because: The role membership exists (e.g., Entra group → db_cdcreader) But the role itself is not included or resolved in the export model Result: Membership → cannot resolve target role → validation failure (SQL71501) This behavior aligns with documented patterns where CDC roles are excluded or not recognized during BACPAC export. Reproducing the Issue You are likely impacted if: CDC is enabled Users or Entra groups are assigned to: db_cdcreader cdc_admin Export is attempted via: SqlPackage SSMS “Export Data-tier Application” Workarounds Option 1: Temporarily remove role membership Remove the CDC role membership before export: ALTER ROLE db_cdcreader DROP MEMBER [your_user_or_group]; Run export, then reassign: ALTER ROLE db_cdcreader ADD MEMBER [your_user_or_group]; This is the simplest and most reliable workaround Confirmed in Microsoft Q&A guidance for CDC roles Option 2: Export from a cleaned database copy If you cannot modify production (e.g., tooling restrictions): Create a database copy Remove CDC-related role memberships Export from the copy Recommended when: Using automation tools (e.g., Commvault) Production changes are restricted Option 3: Cleanup unsupported references General best practice: Remove unsupported / system-bound references before export Especially: CDC role memberships Legacy system objects Option 4: Use SqlPackage with ExtractAllTableData=True Another practical workaround is to leverage the SqlPackage option ExtractAllTableData=True, which allows you to extract all data from all user tables. When set to True: Data is extracted from all user tables You cannot specify individual tables When set to False (default): You can selectively extract data from specific tables only This reduces exposure to unsupported or problematic objects during validation Example SqlPackage /Action:Extract /SourceServerName:<server> /SourceDatabaseName:<database> /TargetFile:<output.bacpac> /p:ExtractAllTableData=True Additional Validation: CDC Role Ownership When investigating export failures involving CDC roles, it is also recommended to verify the ownership of the db_cdcreader role. SqlPackage relies on schema metadata to determine whether an object should be treated as a system-managed CDC object or as a user-defined database object during model validation. If the ownership of db_cdcreader has been changed from its default value, SqlPackage may not correctly identify it as a system CDC role, which can contribute to validation errors such as SQL71501. How to Validate SELECT name, USER_NAME(owning_principal_id) AS role_owner FROM sys.database_principals WHERE name = 'db_cdcreader'; Expected result: db_cdcreader dbo If the role owner is not dbo, the following can be used as a resolution: Resolution “ ALTER AUTHORIZATION ON ROLE::[db_cdcreader] TO [dbo]; “ Maintaining the default ownership ensures that SqlPackage can correctly recognize db_cdcreader as a system-managed CDC role during export operations When to use this option When the export fails due to CDC roles or related schema validation issues (SQL71501) As a targeted workaround when full export is blocked Important Considerations This is not a runtime database issue It is a schema validation limitation in DacFx / SqlPackage CDC itself is supported, but: Certain security objects are not fully exportable Key Takeaways SQL71501 during export is often a model validation issue, not a data issue CDC roles (db_cdcreader, cdc_admin) can break export due to partial modeling The failure is triggered by role membership, not CDC itself Workarounds involve: Removing memberships Exporting from a cleaned copyLessons Learned #550: From a Support Case to Reusable Knowledge
Reaching Lessons Learned #550 is an important milestone for me. However, the value of this series is not only the number of articles published. Each article started with a technical question, an unexpected behavior, a support investigation, or a scenario that required additional testing and analysis. Some cases resulted in a configuration change. Others required a query, a script, a workaround, a product clarification, or a different troubleshooting approach. Over time, I have learned that resolving the immediate issue is only one part of the work. A support case becomes even more valuable when the knowledge gained during the investigation can help another engineer or customer facing a similar situation. Every support case may contain a lesson. The challenge is to identify it, validate it, and make it reusable. Identify the Reusable Lesson Not every detail from a support case needs to become an article. The first step is to identify the part of the investigation that may be useful outside the original scenario. This could be: an unexpected product behavior; a common misunderstanding; a diagnostic query; a troubleshooting method; a configuration requirement; a limitation that may not be immediately visible; a way to interpret a metric or error message; a test that helped confirm the technical explanation. For example, the specific customer environment may be unique, but the method used to distinguish CPU pressure from Data IO pressure may be useful in many other investigations. Similarly, the original application architecture may be complex, but the test used to isolate a network path may be simple and reusable. The objective is not to reproduce the complete support case. The objective is to extract the lesson that may help others. Explain the Symptom Clearly A useful technical article should begin with a behavior that readers can recognize. For example: Connections fail only from one application instance. Query duration increases after a service-tier migration. CPU reaches a high percentage, but the workload remains constrained by another resource. A failover restores normal operation without fully explaining the original cause. A monitoring result appears different from what was initially expected. A reader should be able to determine quickly whether the scenario resembles a problem they are investigating. Describe How the Conclusion Was Reached A solution is more useful when the reader understands how it was validated. For that reason, I normally try to explain: what was initially observed; which evidence was reviewed; which possibilities were considered; which tests were performed; what result supported the conclusion; which limitations remained. The objective is to provide enough context for the reader to understand why the conclusion is reasonable and under which conditions it applies. Separate Mitigation from Explanation A mitigation may restore service without fully explaining the technical cause. For example: restarting an application may reset the connection pool; a failover may disconnect blocking sessions; scaling may increase several resource limits simultaneously; recompiling a query may temporarily produce a better execution plan; reverting a deployment may remove the immediate impact. These actions can be valid and necessary. However, when converting the case into reusable knowledge, it is important to distinguish between: what restored normal operation; what was confirmed as the contributing condition; what remained unconfirmed. This distinction helps prevent a successful recovery action from being interpreted as a complete root-cause explanation. Include Something Practical The most useful articles normally provide something the reader can apply. This may be: a query; a script; a checklist; a sequence of tests; a monitoring recommendation; a comparison table; a list of questions to ask; an example of the expected and unexpected results. Even a short article can be valuable if it gives the reader a practical next step. For example, a troubleshooting article may suggest comparing: affected and unaffected periods; successful and unsuccessful connections; current and previous execution plans; CPU, Data IO, and log write utilization; the original and alternative network paths; behavior before and after one controlled change. The practical element is what transforms an explanation into a reusable resource. Document the Boundaries of the Conclusion A technical conclusion is more reliable when its limitations are clearly described. During a support investigation, the available evidence may not allow us to determine every detail. For example: the historical telemetry may be limited; the behavior may not be reproducible; the exact application request may not be identifiable; the test environment may differ from production; an internal implementation detail may not be externally visible. In these situations, it is useful to explain both what was confirmed and what could not be confirmed. For example: The behavior was reproduced only through the affected network path. The same endpoint and authentication method worked successfully through an alternative path. The tests confirmed that the network path was a relevant condition, although the available evidence did not identify the specific component responsible. This type of conclusion is precise, useful, and transparent. A Simple Model I Normally Follow When deciding whether a support investigation can become reusable knowledge, I normally consider the following sequence: Observe: What behavior was reported or measured? Clarify: What was the exact scope and impact? Investigate: Which evidence was relevant? Reproduce: Could the behavior be tested under controlled conditions? Validate: Which result supported or challenged the explanation? Mitigate: What action reduced the immediate impact? Conclude: What did the available evidence allow us to confirm? Share: Which part of the investigation may help someone else? Not every case follows these steps in the same order, and not every investigation provides a complete answer. However, this approach helps transform an individual technical experience into something that can be understood and reused. Questions That Help Identify a Lessons Learned Article Before writing an article, I normally consider questions such as: Was the behavior unexpected or difficult to interpret? Could the same question affect other Azure SQL users? Was there an important difference between the initial assumption and the final conclusion? Did the investigation produce a useful query, script, or test? Is there a limitation or condition that should be better understood? Can the scenario be explained without customer-specific information? What should another engineer or customer do when facing the same behavior? If the investigation provides a useful answer to one or more of these questions, it may contain a lesson worth sharing. Conclusion After 550 Lessons Learned articles, the most important lesson may be that technical support knowledge should not remain only inside an individual service request. A support case starts with an immediate need: understand the behavior, reduce the impact, and identify the appropriate next action. However, once the investigation is complete, we have an opportunity to go one step further. We can extract the reusable part of the experience, explain how the conclusion was reached, document its limitations, and provide something practical for the next person facing a similar situation. That is how an individual support case can become shared technical knowledge. Resolving a case helps one specific situation. Sharing the validated lesson may help many others avoid starting the same investigation from zero.SQL Data Sync Retirement: Migration Insights and Modern Alternatives
What started as a routine customer discussion quickly evolved into a strategic modernization conversation. A service that had quietly synchronized business-critical data for years was approaching retirement, prompting an important question: What should organizations do next? Every service retirement is an opportunity to reassess architecture, reduce technical debt, and build for the future. When a Retirement Notification Becomes a Business Conversation Recently, while working with a customer, we reviewed their Azure SQL Database architecture and discovered a critical dependency on SQL Data Sync For years, the service had reliably synchronized data across multiple databases, enabling applications, reporting workloads, and distributed business processes. Like many organizations, the customer viewed Data Sync as infrastructure that simply worked in the background. However, the discussion took a different turn when we reviewed Microsoft's retirement announcement: Azure SQL Data Sync will be retired on September 30, 2027. What initially appeared to be a migration challenge quickly became an opportunity to modernize the customer's data movement architecture and align with Microsoft's future investments in data integration, analytics, and cloud-native services. Understanding SQL Data Sync Azure SQL Data Sync was designed to synchronize selected data between Azure SQL Databases and, in some cases, between Azure and on-premises databases. Organizations have commonly used Data Sync for: Hybrid data synchronization Distributed application architectures Globally distributed applications Bi-directional data synchronization While the service has served customers well, organizations should begin evaluating alternative solutions now to ensure sufficient planning, testing, and adoption time before retirement. Because both databases were already in Azure, the discussion quickly moved toward identifying strategic alternatives. Customer's Setup This particular customer had a simple, familiar layout: one Azure SQL Database feeding another, both fully in Azure, connected by SQL Data Sync. No on-premises leg, no complicated topology — just two cloud databases that needed to stay aligned. Their requirements were equally straightforward, and honestly, the kind every team asks for: Reliable, dependable synchronization Low operational overhead — nobody wanted a new system to babysit Something with a real future, not another service on a retirement countdown Room to scale as data volumes grow An Azure-native fit, not a bolt-on third-party tool Because both databases already lived in Azure, the conversation moved quickly toward the platform's own native tooling — starting with the option that ended up being the strongest fit. Option 1: Azure Data Factory (Recommended Strategy) For customers running Azure SQL Database to Azure SQL Database synchronization, Azure Data Factory (ADF) emerged as the strongest strategic recommendation. Why ADF? Azure Data Factory provides: Fully managed Azure-native data movement Enterprise-grade monitoring Flexible orchestration Scalability from development through production environments Long-term Microsoft investment and support The migration pattern we typically recommend looks like: Phase1 :Initial Full Load Before anything can stay in sync, both sides need to start from the same place. Phase 1 is a one-time bulk copy: Azure Data Factory reads everything from the source database and writes it into the target, establishing a clean baseline. Phase2 :Incremental Synchronization Once both databases match, you don't need to keep copying everything — just what's changed. This is where Change Tracking (CT) or Change Data Capture (CDC) comes in: SQL Server-native features that flag which rows were inserted, updated, or deleted since the last run. ADF's incremental pipeline reads only those deltas and applies them downstream, on whatever schedule the business needs — minutes, hours, or daily. Phase 3: Scheduling and Monitoring Once both phases are live, ADF takes over the operational side: scheduling pipeline runs, monitoring their health, retrying failures automatically, and alerting your team when something needs attention. That's a level of visibility SQL Data Sync's built-in sync groups never really offered ADF handles: Scheduling Pipeline execution Monitoring Retry mechanisms Alerting This model often delivers greater visibility and operational control than traditional SQL Data Sync implementations. When You Don't Need Synchronization — You Need a Copy Partway through the engagement, one question reframed the whole discussion: do we actually need two-way synchronization, or do we just need a readable copy of the database somewhere else? That distinction matters more than it sounds, and it points to three other options worth knowing. Option 1: Active Geo-Replication If the goal is disaster recovery, serving read traffic closer to users, or keeping the business running through a regional outage, Active Geo-Replication is usually a better fit than rebuilding sync logic from scratch. It gives you a continuously updated, readable secondary — not a bi-directional sync target. Best fit Disaster recovery scenarios Read-intensive applications Global user distribution Secondary readable databases Less ideal for Complex data transformations Bi-directional updates Option 2: Database Copies and Read Replicas Some organizations do not require continuous synchronization at all. In those cases: Read Replicas Database Copy Good Use Cases Reporting databases Analytics environments Refreshable staging systems Read-only workloads This approach significantly reduces architectural complexity while still meeting many business requirements. Option 3: Microsoft Fabric Mirrored Databases As Microsoft Fabric adoption grows, another interesting alternative is Fabric Mirrored Databases. This option is particularly attractive for organizations already investing in: Microsoft Fabric OneLake Real-time analytics AI and data platform modernization Benefits include: Near real-time data availability Simplified analytics architecture Integration with Fabric workloads Reduced data silos For customers modernizing both operational and analytical platforms, this can be an excellent opportunity to rethink data architecture beyond simple synchronization. Option 4: Azure Functions for Event-Driven Synchronization Not every customer requires a large orchestration platform. For lightweight or application-specific synchronization logic, Azure Functions may offer a more agile approach. Example Use Cases Event-driven updates Custom business rules Microservices architectures Low-volume synchronization requirements The tradeoff is that customers assume additional development and operational responsibilities Lessons Learned from the Customer Engagement This engagement reinforced several important lessons: Don't Wait Until 2027 Although retirement is over a year away, large organizations often require significant planning, testing, governance approvals, and deployment cycles. Starting early reduces risk. There Is No Universal Replacement The right solution depends on: Latency requirements Read versus write workloads Synchronization direction Operational complexity DR requirements Budget limitation Different use cases require different migration paths. 3. Migration Is an Opportunity Rather than simply replacing SQL Data Sync, organizations should evaluate: Data architecture modernization Observability improvements Operational simplification Fabric adoption opportunities Long-term cloud strategy Final Recommendations For most customers currently using Azure SQL Database → Azure SQL Database synchronization: Requirement Recommended Solution Ongoing synchronization Azure Data Factory + CDC/Change Tracking Read-only replica Active Geo-Replication Simple duplication Database Copy or Read Replica Analytics modernization Fabric Mirrored Databases Event-driven custom logic Azure Functions In my customer's use case, Azure Data Factory with incremental changes (CDC) emerged as the preferred strategic path because it was Azure-native, scalable, supported long-term, and aligned with Microsoft's future direction for data movement and integration. Closing Thoughts Technology retirements often create urgency, but they also create opportunity. retirement of SQL Data Sync is not merely a migration project. It is an opportunity to reassess data movement architecture, improve resiliency, reduce technical debt, and embrace modern Azure-native services. If your organization is currently using SQL Data Sync, now is the right time to inventory your sync groups, identify dependencies, and begin evaluating alternative architectures before September 30, 2027. References SQL Data Sync Retirement Migration Guide What is SQL Data Sync for Azure SQL Database? SQL Data Sync retirement: Migrate to alternative solutionsLessons Learned #547:Some SQL DB Statistics Remain Outdated While Others Are Automatically Updated
During the analysis of a SQL Server performance case, we observed an interesting statistics update pattern on a large table. Several statistics had recently been updated at different times, while a group of automatically created _WA_Sys_ statistics still showed: An older last_updated date. A high modification_counter. A row count significantly lower than the current number of rows in the table. At first glance, this could suggest that AUTO_UPDATE_STATISTICS was not working correctly. However, a closer review showed that this pattern can be completely consistent with the expected behavior of SQL Server. 1. Types of statistics in SQL Server SQL Server can maintain several types of statistics. Automatically created column statistics When AUTO_CREATE_STATISTICS is enabled, SQL Server can automatically create a single-column statistic when the Query Optimizer needs cardinality information for a column used in a query predicate. These statistics normally use names such as: _WA_Sys_00000002_47A6D5E5 The name can be interpreted as: _WA_Sys_<column_id in hexadecimal>_<object_id in hexadecimal> For example: 00000002 hexadecimal = column_id 2 47A6D5E5 hexadecimal = table object_id The most reliable way to identify the associated column is not to decode the name manually, but to query the SQL Server catalog views: DECLARE @TableName sysname = N'dbo.CustomerTransactions'; SELECT s.stats_id, s.name AS statistics_name, sc.stats_column_id, c.column_id, c.name AS column_name, s.auto_created, s.user_created, s.no_recompute FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID(@TableName) AND s.name = N'_WA_Sys_00000002_47A6D5E5' ORDER BY sc.stats_column_id; Statistics associated with indexes When SQL Server creates an index, it also creates a statistics object associated with the index. For example: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId) this creates an index statistics object normally named: IX_CustomerTransactions_ClientId when a statistics object corresponds to an index, its stats_id matches the index's index_id. User-created statistics Statistics can also be created explicitly: CREATE STATISTICS ST_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId ); This object is identified in sys.stats with: user_created = 1 auto_created = 0 and AUTO_UPDATE_STATISTICS applies to index statistics, automatically created single-column statistics, manually created statistics and filtered statistics. 2. Statistics are not updated together Assume that a table has these statistics: _WA_Sys_00000002_xxxxxxxx for ClientId _WA_Sys_00000003_xxxxxxxx for StatusId IX_CustomerTransactions_CreatedDate for CreatedDate PK_CustomerTransactions and TransactionId After a large data load, several or all of them could become eligible for an automatic update. However, SQL Server does not immediately update all eligible statistics. Before compiling a query, the Query Optimizer identifies the statistics that could be relevant to the query predicates and checks whether those statistics are outdated. Consider this query: SELECT TransactionId, ClientId, Amount FROM dbo.CustomerTransactions WHERE ClientId = 100; The optimizer might need a histogram on ClientId. If the corresponding statistics object is outdated and has crossed its update threshold, SQL Server may update that specific statistics object. It does not need to update unrelated statistics on: StatusId CreatedDate As a result, statistics on the same table can legitimately have different update times: PK_CustomerTransactions 2026-07-22 10:53 IX_CustomerTransactions_ClientId 2026-07-22 10:50 IX_CustomerTransactions_CreatedDate 2026-07-22 10:13 _WA_Sys_00000003_47A6D5E5 2026-07-19 10:00 This pattern can be evidence of demand-driven automatic updates rather than evidence of a malfunction. 3. What does modification_counter represent? The modification_counter returned by sys.dm_db_stats_properties represents the number of modifications made to the leading statistics column since that statistics object was last updated. This definition is especially important for multicolumn statistics. For example: CREATE INDEX IX_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId ); The associated statistics object contains: Histogram: ClientId Density information: ClientId ClientId, StatusId The histogram and modification_counter are based on the leading column, ClientId. If only StatusId is modified: UPDATE dbo.CustomerTransactions SET StatusId = 2 WHERE TransactionId = 100; this does not have the same statistics impact as changing ClientId, because ClientId is the leading histogram column. SQL Server statistics contain only one histogram, built on the first key column. Multicolumn statistics additionally contain density information for column prefixes. 4. Why do multiple statistics sometimes have similar modification counters? This commonly happens after an insert operation. For example: INSERT INTO dbo.CustomerTransactions ( TransactionId, ClientId, StatusId, Amount, CreatedDate ) SELECT TransactionId, ClientId, StatusId, Amount, CreatedDate FROM dbo.StagingCustomerTransactions; Each inserted row introduces a value for every populated column. Consequently, multiple single-column statistics may show similar increases in their modification counters. This is particularly visible after a large ETL operation: Table rows before the load: 487,673 Rows inserted by the ETL: 515,244 Current approximate row count: 1,002,917 Several statistics could then show modification counters close to the number of inserted rows. That does not mean SQL Server must update all those statistics immediately. They become candidates for updating, but an update is normally triggered when query optimization requires them. 5. Does automatic updating apply only to _WA_Sys_ statistics? AUTO_UPDATE_STATISTICS applies to: Automatically created _WA_Sys statistics Index statistics Primary-key index statistics User-created statistics Each statistics object is evaluated independently. Therefore, SQL Server might update: PK_CustomerTransactions while leaving this object unchanged: _WA_Sys_00000003_47A6D5E5 The reverse is also possible. The behavior depends on which statistics are considered relevant during compilation or cached-plan validation. 6. What happens when a _WA_Sys_ statistic and an index statistic cover the same column? This is one of the most interesting scenarios. Assume SQL Server originally created: _WA_Sys_00000002_xxxxxxxx for ClientId. Later, someone creates this index: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId); The table now has two statistics objects with histograms on ClientId: _WA_Sys_00000002_xxxxxxxx and IX_CustomerTransactions_ClientId Conceptually: _WA_Sys statistic Histogram on ClientId Index statistic Histogram on ClientId For a query such as: SELECT * FROM dbo.CustomerTransactions WHERE ClientId = @ClientId; the optimizer can have more than one potentially relevant statistics object. It may rely on the index statistics object rather than the older _WA_Sys_ object. In that case: IX_CustomerTransactions_ClientId Updated recently _WA_Sys_00000002_xxxxxxxx Old last_updated value High modification_counter This does not necessarily mean that automatic statistics updating has failed. It can mean that the _WA_Sys_ statistic has become redundant and has not been required by recent compilations. However, this should be presented carefully: The Query Optimizer is not publicly documented as always preferring an index statistic over an equivalent _WA_Sys_ statistic. The statistics selected can depend on: Query. Predicates. Available indexes. Filtered versus unfiltered statistics. Statistics freshness. Sampling quality. Multicolumn density information. Cardinality Estimator behavior. Existing cached plans. The correct conclusion is that the scenario is possible and plausible, but the execution plan should be inspected before claiming that a specific statistics object was used. As we wrote down in multiple articles in our blog (below), identify redudant statistics is part of DBA work to avoid this situation, also, in other situations, I saw that the maintenance plan is taking too much time because we are updating statistics that we are not using or migth be duplicated. The following script identifies statistics whose leading columns overlap: DECLARE @TableName sysname = N'dbo.CustomerTransactions'; ;WITH LeadingStatisticsColumns AS ( SELECT s.object_id, s.stats_id, s.name AS statistics_name, s.auto_created, s.user_created, s.no_recompute, s.has_filter, s.filter_definition, sc.column_id, c.name AS leading_column, i.index_id, i.name AS index_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id AND sc.stats_column_id = 1 INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id LEFT JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id WHERE s.object_id = OBJECT_ID(@TableName) ) SELECT leading_column, statistics_name, CASE WHEN index_id IS NOT NULL THEN N'INDEX STATISTICS' WHEN auto_created = 1 THEN N'AUTO-CREATED _WA_SYS' WHEN user_created = 1 THEN N'USER-CREATED STATISTICS' ELSE N'OTHER' END AS statistics_type, index_name, has_filter, filter_definition, no_recompute, COUNT(*) OVER ( PARTITION BY column_id ) AS statistics_on_same_leading_column FROM LeadingStatisticsColumns ORDER BY leading_column, statistics_type, statistics_name; This does not automatically mean that one object should be deleted. It only identifies an overlap. 7. Script The following example demonstrates how an automatically created statistic can coexist with a later index statistic. DROP TABLE IF EXISTS dbo.CustomerTransactions; GO CREATE TABLE dbo.CustomerTransactions ( TransactionId int NOT NULL, ClientId int NOT NULL, StatusId tinyint NOT NULL, Amount decimal(12,2) NOT NULL, CreatedDate datetime2(0) NOT NULL, CONSTRAINT PK_CustomerTransactions PRIMARY KEY CLUSTERED (TransactionId) ); GO Insert sample data ;WITH Numbers AS ( SELECT TOP (200000) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b ) INSERT INTO dbo.CustomerTransactions ( TransactionId, ClientId, StatusId, Amount, CreatedDate ) SELECT n, n % 5000, n % 5, CONVERT(decimal(12,2), n % 10000), DATEADD ( minute, -(n % 100000), SYSUTCDATETIME() ) FROM Numbers; GO Ensure that automatic statistics creation and updating are enable ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS ON; GO ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON; GO Trigger automatic statistics creation on ClientId SELECT COUNT_BIG(*) FROM dbo.CustomerTransactions WHERE ClientId = 100 OPTION (RECOMPILE); GO Check the statistics created for ClientId SELECT s.stats_id, s.name AS statistics_name, s.auto_created, s.user_created, c.name AS column_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id INNER JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.object_id = OBJECT_ID(N'dbo.CustomerTransactions') AND c.name = N'ClientId' ORDER BY s.stats_id; Create an index on the same column CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId); The table can now have: Modify the data significantly UPDATE dbo.CustomerTransactions SET ClientId = ClientId + 10000 WHERE TransactionId <= 100000; Review the counters again SELECT s.name AS statistics_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats AS s OUTER APPLY sys.dm_db_stats_properties ( s.object_id, s.stats_id ) AS sp WHERE s.object_id = OBJECT_ID(N'dbo.CustomerTransactions') ORDER BY s.stats_id Force a new compilation using ClientId SET STATISTICS XML ON; GO SELECT COUNT_BIG(*) FROM dbo.CustomerTransactions WHERE ClientId = 10100 OPTION (RECOMPILE); GO SET STATISTICS XML OFF; GO After the query, review: The actual execution plan XML. The StatisticsInfo elements. last_updated. modification_counter. The exact object updated is an optimizer decision and can vary by SQL Server version, build, compatibility level and query shape. The test should therefore be used to observe the behavior rather than to assume a fixed preference. Finally, as you could see SQL Server choose _WA_Sys_00000002_151102AD instead of IX_CustomerTransactions_ClientId to update. In some situations, depending on execution plan, SQL Server might choose IX_CustomerTransactions_ClientId to update instead of _WA_Sys_00000002_151102AD and for this reason, doesn't mean that SQL Server is not updating the statistics it is depending that it is choosing one of them that the column is involved. My lessons learned, a statistic can be outdated without being relevant, and it can be relevant without being the only available source of cardinality information. Before interpreting an old last_updated value as an automatic statistics failure, identify the leading column, look for overlapping statistics, inspect the execution plan and determine whether there is a real estimation or performance problem. Articles: Lesson Learned #482: Identifying Potential Duplicate Statistics | Microsoft Community Hub Lesson Learned #324: Query Recompilation in Azure SQL | Microsoft Community Hub Lessons Learned #537: Copilot Prompts for Troubleshooting on Azure SQL Database | Microsoft Community Hub Lesson Learned #498:Understanding the Role of STATMAN in SQL Server and Its Resource Consumption | Microsoft Community Hub Disclaimer: The scripts included in this article are provided for demonstration and educational purposes only. They create a sample table, insert a significant number of rows, create indexes and statistics, modify data, and change automatic statistics settings in the current database. Run the complete demonstration only in a test or non-production environment. Review and adapt the database name, object names, row volume, and statements before execution. The results may vary depending on the SQL Server version, database compatibility level, existing configuration, data distribution, and workload. Always test the scripts in a representative environment before applying any conclusion or change to a production system.Lessons Learned #546: Maintaining a Local Azure Resource Inventory
I worked on a service request that our customer has an application works repeatedly with the same Azure resources. I guess that it may be useful to maintain our own persistent inventory instead of retrieving and validating every resource individually during each execution. In this example, a PowerShell script maintains an inventory of Azure SQL logical servers in a local JSON file. The idea is: Load the local JSON inventory -> Query Azure Resource Graph -> Compare both inventories -> Validate only detected changes -> Update the JSON file. Azure Resource Graph provides the current list of Microsoft.Sql/servers resources. The result is compared with the inventory stored by the application. If a server exists in both inventories, it is marked as: Observed: No additional request is required. NewValidatedByPointGet:If a new server is detected, it is validated individually with Get-AzSqlServer before being added If a previously known server is missing from the current result, the script also validates it individually. The possible results are: RecoveredByPointGet: the server still exists and remains in the inventory. Deleted: the individual request returns ResourceNotFound, so the server is removed. UnknownRetainedFromCache: the validation is inconclusive, so the previous inventory entry is preserved. The JSON file therefore represents the application’s active resource inventory and remains available between executions. I think this approach reduces repeated API requests because individual validation is performed only when a resource is new, missing, or has changed. I would like to share the PowerShell Script. Set-StrictMode -Version Latest $ErrorActionPreference = "Stop" # ------------------------------------------------------------ # Configuration # ------------------------------------------------------------ $tenantId = "<tenant-id>" $subscriptionId = "<subscription-id>" $resourceGroup = "<resource-group>" $cacheFile = ".\sql-server-inventory.json" # Required modules: # Install-Module Az.ResourceGraph -Scope CurrentUser # Install-Module Az.Sql -Scope CurrentUser # Connect-AzAccount -Tenant $tenantId Set-AzContext ` -Tenant $tenantId ` -Subscription $subscriptionId ` -ErrorAction Stop | Out-Null # ------------------------------------------------------------ # Helper functions # ------------------------------------------------------------ function ConvertTo-NormalizedResourceId { param( [Parameter(Mandatory)] [string]$ResourceId ) return $ResourceId.Trim().TrimEnd("/").ToLowerInvariant() } function Test-IsResourceNotFound { param( [Parameter(Mandatory)] [System.Management.Automation.ErrorRecord]$ErrorRecord ) $errorText = @( $ErrorRecord.Exception.Message $ErrorRecord.ErrorDetails.Message $ErrorRecord.FullyQualifiedErrorId $ErrorRecord.ToString() ) -join " " return ( $errorText -match "(?i)(\b404\b|ResourceNotFound|ServerNotInSubscriptionResourceGroup)" ) } function New-InventoryItem { param( [Parameter(Mandatory)] [string]$ResourceId, [Parameter(Mandatory)] [string]$Name, [string]$Location, [Parameter(Mandatory)] [string]$ValidationStatus ) return [pscustomobject]@{ id = $ResourceId name = $Name location = $Location validationStatus = $ValidationStatus } } # ------------------------------------------------------------ # 1. Load the persistent inventory # ------------------------------------------------------------ if (Test-Path -LiteralPath $cacheFile) { $jsonContent = Get-Content ` -LiteralPath $cacheFile ` -Raw ` -ErrorAction Stop if ([string]::IsNullOrWhiteSpace($jsonContent)) { $cachedServers = @() } else { $cachedServers = @( $jsonContent | ConvertFrom-Json ` -ErrorAction Stop ) } } else { $cachedServers = @() } $cachedServersById = @{} foreach ($cachedServer in $cachedServers) { $resourceId = [string]$cachedServer.id if ([string]::IsNullOrWhiteSpace($resourceId)) { continue } $normalizedId = ConvertTo-NormalizedResourceId ` -ResourceId $resourceId $cachedServersById[$normalizedId] = $cachedServer } Write-Host "Stored inventory: $($cachedServersById.Count) server(s)" # ------------------------------------------------------------ # 2. Discover the current resources # ------------------------------------------------------------ $query = @" Resources | where subscriptionId =~ '$subscriptionId' | where resourceGroup =~ '$resourceGroup' | where type =~ 'microsoft.sql/servers' | project id = tostring(id), name = tostring(name), location = tostring(location) "@ try { $argResponse = Search-AzGraph ` -Query $query ` -Subscription $subscriptionId ` -First 1000 ` -ErrorAction Stop } catch { throw ( "Azure Resource Graph query failed. " + "The existing inventory has not been modified. " + "Error: $($_.Exception.Message)" ) } if ( $null -ne $argResponse -and $argResponse.PSObject.Properties.Name -contains "Data" ) { $currentServers = @($argResponse.Data) } else { $currentServers = @($argResponse) } $currentServersById = @{} foreach ($currentServer in $currentServers) { $resourceId = [string]$currentServer.id $serverName = [string]$currentServer.name if ( [string]::IsNullOrWhiteSpace($resourceId) -or [string]::IsNullOrWhiteSpace($serverName) ) { continue } $normalizedId = ConvertTo-NormalizedResourceId ` -ResourceId $resourceId $currentServersById[$normalizedId] = $currentServer } Write-Host "Current observation: $($currentServersById.Count) server(s)" # ------------------------------------------------------------ # 3. Build the synchronized active inventory # ------------------------------------------------------------ $activeInventoryById = @() $activeInventoryIndex = @{} $deletedServers = @() foreach ($normalizedId in $currentServersById.Keys) { $currentServer = $currentServersById[$normalizedId] $resourceId = [string]$currentServer.id $serverName = [string]$currentServer.name $location = [string]$currentServer.location if ($cachedServersById.ContainsKey($normalizedId)) { # The resource is present in both inventories. $item = New-InventoryItem ` -ResourceId $resourceId ` -Name $serverName ` -Location $location ` -ValidationStatus "Observed" $activeInventoryIndex[$normalizedId] = $item continue } # The resource is new. Validate it individually. Write-Host "Validating new server '$serverName'..." try { $validatedServer = Get-AzSqlServer ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -ErrorAction Stop $item = New-InventoryItem ` -ResourceId $resourceId ` -Name ([string]$validatedServer.ServerName) ` -Location ([string]$validatedServer.Location) ` -ValidationStatus "NewValidatedByPointGet" $activeInventoryIndex[$normalizedId] = $item } catch { Write-Warning ( "New server '$serverName' could not be validated " + "and was not added to the inventory. " + "Error: $($_.Exception.Message)" ) } } # ------------------------------------------------------------ # 4. Validate previously known resources missing from discovery # ------------------------------------------------------------ foreach ($normalizedId in $cachedServersById.Keys) { if ($currentServersById.ContainsKey($normalizedId)) { continue } $cachedServer = $cachedServersById[$normalizedId] $resourceId = [string]$cachedServer.id $serverName = [string]$cachedServer.name $location = [string]$cachedServer.location Write-Host ( "Server '$serverName' is missing from the current " + "observation. Running individual validation..." ) try { $validatedServer = Get-AzSqlServer ` -ResourceGroupName $resourceGroup ` -ServerName $serverName ` -ErrorAction Stop $item = New-InventoryItem ` -ResourceId $resourceId ` -Name ([string]$validatedServer.ServerName) ` -Location ([string]$validatedServer.Location) ` -ValidationStatus "RecoveredByPointGet" $activeInventoryIndex[$normalizedId] = $item Write-Warning ( "Server '$serverName' was not returned by discovery, " + "but individual validation confirmed that it still exists." ) } catch { if (Test-IsResourceNotFound -ErrorRecord $_) { $deletedServers += [pscustomobject]@{ id = $resourceId name = $serverName location = $location validationStatus = "Deleted" } Write-Warning ( "Deleted server detected: '$serverName'. " + "It will be removed from the active inventory." ) } else { # The result is inconclusive. Preserve the previous entry. $item = New-InventoryItem ` -ResourceId $resourceId ` -Name $serverName ` -Location $location ` -ValidationStatus "UnknownRetainedFromCache" $activeInventoryIndex[$normalizedId] = $item Write-Warning ( "The status of server '$serverName' could not be " + "confirmed. The previous inventory entry was retained. " + "Error: $($_.Exception.Message)" ) } } } # ------------------------------------------------------------ # 5. Save the updated active inventory # ------------------------------------------------------------ $activeInventory = @( $activeInventoryIndex.Values | Sort-Object name ) $jsonOutput = ConvertTo-Json ` -InputObject $activeInventory ` -Depth 10 $temporaryFile = "$cacheFile.tmp" Set-Content ` -LiteralPath $temporaryFile ` -Value $jsonOutput ` -Encoding utf8 ` -Force Move-Item ` -LiteralPath $temporaryFile ` -Destination $cacheFile ` -Force # ------------------------------------------------------------ # 6. Report the synchronization result # ------------------------------------------------------------ Write-Host "" Write-Host "Active inventory: $($activeInventory.Count) server(s)" $activeInventory | Format-Table ` name, location, validationStatus ` -AutoSize if ($deletedServers.Count -gt 0) { Write-Host "" Write-Warning "Confirmed deleted servers:" $deletedServers | Format-Table ` name, location, validationStatus ` -AutoSize } Disclaimer This PowerShell script is provided as a simplified proof of concept to illustrate a persistent resource inventory pattern. It should be reviewed, tested, and adapted before being used in a production environment. Authentication, permissions, error handling, retry policies, concurrency, logging, inventory storage, and operational requirements may differ between environments. The local JSON file is suitable for demonstration purposes and small automation scenarios.Lessons Learned #544: How to Detect INT Identity Exhaustion Before Inserts Fail.
Recently, I worked on a support case involving an Azure SQL Database table where the customer had reached the maximum value supported by the INT data type. The table used an INT IDENTITY(1,1) column as its primary key. Over time, the generated identity value approached the maximum value supported by INT. Once the available range was exhausted, the application was no longer able to insert new rows. At that point, the column needed to be changed from INT to BIGINT. However, performing this type of migration on a very large table can be a complex and time-consuming operation. The situation is that an INT column uses 4 bytes and supports values from: -2,147,483,648 to: 2,147,483,647. To prevent similar problems in the future, I suggested using the following query to review the current status of all INT IDENTITY columns in the database. The query uses the sys.identity_columns catalog view: SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, CONVERT(bigint, c.last_value) AS CurrentIdentityValue, CONVERT(bigint, 2147483647) AS MaximumIntValue, CONVERT(bigint, 2147483647) - ISNULL(CONVERT(bigint, c.last_value), 0) AS RemainingValues, CAST( ISNULL(CONVERT(decimal(20,2), c.last_value), 0) / 2147483647 * 100 AS decimal(6,2) ) AS PercentUsed FROM sys.identity_columns AS c INNER JOIN sys.tables AS t ON c.object_id = t.object_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE TYPE_NAME(c.system_type_id) = 'int' ORDER BY PercentUsed DESC; I prefer using this query instead of relying on: SELECT COUNT(*) FROM dbo.TableName. The number of rows in a table does not necessarily match the current identity value. Rows might have been deleted, transactions might have been rolled back, and identity values might contain gaps. For this reason, the current identity value is a better indicator of the remaining capacity. Adding this query as a regular preventive check can help identify identity columns that are approaching their limits before they cause application failures. I would like to share with you an example creates a table with an identity seed close to the maximum value supported by INT: DROP TABLE IF EXISTS dbo.IdentityCapacityDemo2; CREATE TABLE dbo.IdentityCapacityDemo2 ( Id INT IDENTITY(2147483600,1) NOT NULL, CreatedDate datetime2(0) NOT NULL CONSTRAINT DF_IdentityCapacityDemo2_CreatedDate DEFAULT SYSUTCDATETIME(), CONSTRAINT PK_IdentityCapacityDemo2 PRIMARY KEY CLUSTERED (Id) ) Insert 20 rows using this command: insert into IdentityCapacityDemo2(CreatedDate) values(SYSUTCDATETIME()) Example of returns: I think runnning this preventive check can help detect identity exhaustion before it affects the application.Lessons Learned #543: Evaluating MultiSubnetFailover with Azure SQL Database
Last week, I worked on a support case in which the use of the MultiSubnetFailover connection-string feature was being considered for an application connecting to Azure SQL Database. The expectation was that enabling the following option could improve connection recovery during a database failover changing MultiSubnetFailover to True. This option is commonly associated with SQL Server high availability, and Azure SQL Database is also designed to remain available by moving databases between replicas when required. However, after reviewing the Azure SQL Database connectivity architecture and comparing the behavior with the property enabled and disabled, I did not observe a clear improvement. The property could be added to the connection string without generating an error, and the application was able to connect successfully with both configurations. What MultiSubnetFailover is designed for MultiSubnetFailover was introduced primarily for SQL Server high-availability configurations such as: Always On Availability Group listeners. SQL Server Failover Cluster Instance virtual network names. In a multi-subnet Availability Group, a listener name may resolve to multiple IP addresses located in different network subnets. Without MultiSubnetFailover=True, the application may try those addresses sequentially. If the first address is not currently active, the connection can be delayed while the attempt waits for a timeout. When the option is enabled, supported SQL client drivers can attempt connections to the listener addresses in parallel and use the first address that responds successfully. This can reduce connection time after an Availability Group failover because the SQL client is directly involved in selecting the reachable listener address. Why Azure SQL Database is different Azure SQL Database uses a different connectivity architecture. The application connects to a logical server endpoint: <server-name>.database.windows.net. The Azure SQL connectivity layer receives the connection and routes it to the infrastructure currently hosting the database. Depending on the configured connection policy, the Azure SQL gateway either proxies the connection or redirects the application to the appropriate database node. The important difference is that the SQL client does not receive a list containing the IP addresses of the Azure SQL Database primary and secondary replicas. The decision and the associated routing are managed by the Azure SQL Database platform. Although Azure SQL Database internally uses multiple replicas for high availability, this is not the same connectivity model as a SQL Server Availability Group listener that publishes multiple addresses through DNS. What about Failover Groups? Azure SQL Database Failover Groups provide a stable listener endpoint such as: <failover-group-name>.database.windows.net. Following a regional failover, the listener is updated so that it points to the logical server hosting the new primary databases. This process depends partly on DNS. The listener name remains the same, but its DNS target changes after the failover. This is still different from a SQL Server multi-subnet Availability Group listener. The Failover Group listener does not expose the addresses of the Azure SQL Database replicas to the SQL client. Therefore, MultiSubnetFailover=True cannot directly select the new primary replica. In this scenario, application recovery continues to depend on the service transition, DNS resolution, and retry behavior. The importance of retry logic One of the main lessons from this case was that retry logic is more relevant to Azure SQL Database resiliency than enabling MultiSubnetFailover. An application connecting to Azure SQL Database must expect occasional transient connectivity errors. These can occur during maintenance, scaling, failover, network interruptions, or temporary service conditions. An appropriate retry strategy should normally include: A limited number of retry attempts. A short delay before the first retry. Increasing delays between subsequent attempts. A maximum retry interval. Creation of a fresh SQL connection. For transactions, retry logic requires additional care. The application must determine whether the transaction was committed, rolled back, or left in an unknown state before repeating the complete operation.Azure SQL DB Fabric Mirroring with Private Endpoint
Introduction Overview steps for configuration of Mirroring between Azure SQL Database to Fabric Mirrored Database over Private Endpoint and Public Connectivity Disabled on source. Prerequisites #1 - The minimum requirement for the source Azure SQL Database tier is - it is Standard Tier with DTUs equal or greater than 100. Free, Basic Tier, or <100 DTUs are NOT supported. All vCore model tiers supported. #2 - System Assigned Managed Identity (SAMI) must be enabled on the Azure SQL logical server. #3 - Microsoft.PowerPlatform should be registered as a source provider at the subscription level. If this step is not completed, you'll face error in the next steps, while creating the 'Virtual Network Data Gateway', example below. #4 - The Virtual Network Subnet of the configured Private Endpoint should have the following selected. Select Microsoft.PowerPlatform/netaccesslinks for the Subnet Delegation tab. This is a required step, otherwise the subnet is grayed out to select while configuration of the Virtual Network Data Gateway at Fabric level. High Level Configuration Steps #1 - Go to Fabric Portal > Settings Click on Settings button on top right > Click on Manage Connections and Gateways Go to 'Virtual Network Data Gateway' tab > Click New In the new page, Select your Capacity, Subscription, Resource Group, VNET and Subnet of the source Azure SQL DB and create it. #2 - Go back to your workspace, and click new item > Search 'Mirrored Azure SQL Database' #3 - Here, in Data Gateway section, chose your new created gateway which we created in previous step, and fill the required source Azure SQL Database details and click connect. #4 - Select the tables to be mirrored in the next steps and you will be able to successfully mirror from Azure SQL Database to Mirrored Azure SQL Database without Public Connectivity and using Private Endpoint.
Lessons Learned #542: Reviewing Historical Azure SQL Database Storage Growth
This week I worked on a service request where our customer needed to understand how an Azure SQL Database had grown over time. This information can be useful for capacity planning, cost analysis, and performance reviews. There are several possible approaches, depending on whether we need to review recent historical data that is still available in Azure Monitor, or whether we need to start collecting long-term historical data from now on. In this lesson learned, I would like to summarize some of the options available. 1. Reviewing recent historical data using Azure Monitor metrics The first point to clarify is how Azure Monitor metrics retention works. Most Azure platform metrics are retained for up to 93 days. However, a single Azure Monitor Metrics chart can query no more than 30 days of data at a time. This means that, if the data is still within the Azure Monitor retention window, we might need to review the metric in 30-day intervals. For Azure SQL Database storage usage, the metric commonly used is Data space used 2. Exporting metrics to Log Analytics for long-term analysis If the requirement is to perform long-term analysis, I would like to recommended option is to enable Diagnostic Settings on the Azure SQL Database and send the metrics to a Log Analytics workspace. Azure SQL Database diagnostic telemetry can be exported to different destinations, including: Log Analytics workspace Storage Account Event Hubs Using Log Analytics provides a very flexible way to query, aggregate, and visualize the data by using KQL. Once the metrics are available in Log Analytics, we can calculate the monthly database growth. For example: AzureMetrics | where ResourceProvider =~ "MICROSOFT.SQL" | where ResourceId == "/SUBSCRIPTIONS/your subscription/RESOURCEGROUPS/yourresourcegroup/PROVIDERS/MICROSOFT.SQL/SERVERS/yourserver/DATABASES/yourdatabase" | where MetricName == "storage" | summarize arg_max(TimeGenerated, Average) by Month = startofmonth(TimeGenerated) | project Month, DataSpaceUsedGB = round(Average / 1024 / 1024 / 1024, 2) | order by Month asc This query takes the last available value for each month and converts the metric from bytes to GB. Depending on the analysis requirements, the query can be customized. 3. Creating a custom database space usage history process If we need more control, or if we want to collect more granular database-level information, another option is to create a custom process that periodically captures the current database space usage into a table. This approach can be useful when we want to keep the information inside the database itself and avoid depending on external telemetry storage for this specific requirement. For example, the following table can be used to store daily or weekly snapshots: CREATE TABLE dbo.DatabaseSpaceUsageHistory ( SnapshotTimeUtc datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(), DatabaseName sysname NOT NULL, DataAllocatedMB decimal(19,2) NULL, DataUsedMB decimal(19,2) NULL, DataUnusedMB decimal(19,2) NULL, LogAllocatedMB decimal(19,2) NULL ); --Example collection query: INSERT INTO dbo.DatabaseSpaceUsageHistory ( DatabaseName, DataAllocatedMB, DataUsedMB, DataUnusedMB, LogAllocatedMB ) SELECT DB_NAME() AS DatabaseName, SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8.0 / 1024 AS DataAllocatedMB, SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') END) * 8.0 / 1024 AS DataUsedMB, ( SUM(CASE WHEN type_desc = 'ROWS' THEN size END) - SUM(CASE WHEN type_desc = 'ROWS' THEN FILEPROPERTY(name, 'SpaceUsed') END) ) * 8.0 / 1024 AS DataUnusedMB, SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8.0 / 1024 AS LogAllocatedMB FROM sys.database_files; This process can be executed daily, weekly, or monthly using the automation method that best fits the environment. This approach provides more control over the data collected, the retention period, and the frequency of collection.
