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!


.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:

SELECT d.name
    , mf.name
    , 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 d.name = '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;
| improve this answer | |
  • 1
    Thanks for the quick answer! Apparently sp_spaceused / sp_helpdb showed the space usage correctly after all. What got me confused was comparing them to a slightly older copy of the DB, which contained more unused space amounting to the same total space used. That combined with my not understanding the filesystem .mdf timestamps got me properly confused. Thank you for your time! – Kahn Aug 7 '14 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.