AlwaysOn Secondary Replica DB-size

Posted on

Question :

I just noticed something peculiar. We’re using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, it shows a modification time of several months ago. Similarly, using sp_helpdb ‘DBNAME’ in the secondary replica, it claims the space is significantly less than in the primary node.

Still, the DB shows as being Synchronized in SSMS object explorer, and upon reading the data in the DB, it’s clear that the latest data is being replicated properly.

This is not a problem, I would simply like to understand what’s going on, as I can’t seem to find this information by googling for it. Why doesn’t the space usage seem to be updated properly? Is it a question of permissions, or a symptom of the read-only mode itself, or what?

Thank you!

Answer :

.mdf and .ldf files do not show the last modification date in the way you think it does.

The file-system date reflects the last time the instance started the database. Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database.

Can you add the output of sp_helpdb 'dbname' for both instances to your question? Also add output from:

    , mf.type_desc
    , mf.size * 8192E0 / 1048576 AS FileSizeMB
    , mf.state_desc, mf.max_size * 8192E0 / 1048576 AS MaxFileSizeMB
    , mf.growth  * 8192E0 / 1048576 AS FileGrowthMB
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE = 'dbname';

The following query, when run from the database in question, will show the number of megabytes allocated and used:

SELECT sum(total_pages) * 8192E0 / 1048576 as TotalMB
    , sum(used_pages) * 8192E0 / 1048576 as UsedMB
FROM sys.allocation_units;

Leave a Reply

Your email address will not be published. Required fields are marked *