2

TL;DR: What is the wpr_bucket_table object in a SQL Server database, and how can we reduce the space it takes up?


We have an Azure SQL Database that takes up significantly more storage than expected. We benchmarked it by creating an RDS for SQL Server 2019 database and copied the Azure database's contents over to the RDS database with the Import and Export Wizard in SSMS.

The RDS database takes up ~15MB, which is in line with what we'd expect given the tiny volume of test data, whereas the Azure database takes up ~280MB (almost 20X!).

In an attempt to identify the reason(s) for this difference, we ran the following SQL command, which we found in an Azure troubleshooting article:

-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 AS megabytes
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY megabytes DESC;
GO
name                     megabytes
----                     ---------
wpr_bucket_table         243.257812
backup_metadata_store    3.390625
[etc.]

The same query in RDS shows wpr_bucket_table at 0.00000.

We couldn't find much online documentation about wpr_bucket_table, other than that it probably is associated with something called the "Write Page Recorder" (but then there's no documentation about that either).

  1. What is the wpr_bucket_table object and the "Write Page Recorder"?
  2. Is there something we can do to purge or reduce the size of wpr_bucket_table?

These are test databases we deployed to investigate why our production data set goes from ~5GB in delimited files to ~150GB in Azure SQL. As it turns out, the problem in production is different, but it'd still be nice to get an answer to what this thing is and whether we can get rid of it.

There's no good documentation for it that I've found, but there are quite a few Google results that point to this being a SQL Server thing. It also exists in RDS SQL Server, just doesn't take up any space there.

There's also a Microsoft KB article that touches on it (but doesn't explain what it is):

When you use Always On Availability Groups in Microsoft SQL Server on Linux, a database that belongs to an availability group may be marked as SUSPECT. Additionally, error messages that resemble the following are logged in the SQL Server error log:

Error: 883, Severity: 16, State: 1. Could not create the Write Page Recorder table: wpr_bucket_table for database WritePageRecorder2::CreateWPRBucketTable.

0

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.