Jeff Moden (8/28/2015)

---

ScottPletcher (8/28/2015)

---

Jeff Moden (8/28/2015)

---

Why not just do a [font="Courier New"]SELECT SizeMB = Size/128.0, * FROM sys.Master_Files [/font]and call it a day?

I thought sys.master_files was not always kept current, particularly for tempdb.

As to size, some people may care about those less than 1MB. If they don't, it's very easy to round up/down after the fact, but obviously impossible to add detail size data that's already been rounded away.

Dividing by 128.0 does give 6 decimal places of resolution with the understanding that the answer is in binary MB (1024*1024 bytes). For those that don't know, there are 128 pages in a binary MB each of which is a binary 8K bytes or 8,192 decimal bytes.

SELECT 1/128.0;

If you want actual bytes, case the size as BIGINT and multiply by 8192 (the number of bytes in a page). If you want decimal MB (1,000*1,000), multiply the size by 0.008192.

As for the claim that sys.master_files isn't always kept up to date, I suppose you're referring to the note on the sys.master_files page in BOL, which states...

[font="Arial Black"]Note:[/font]

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.

I'm not sure what it is that they're actually talking about because we all know that even if you drop every object from the database, it will not change the size of the MDF or LDF files. Only "shrink database" or "shrink file" operations will decrease the size of the underlying files. I can see it getting behind if growth is in process but I've never seen it stay far behind.

There's also another "problem" associated with sys.master_files but I'm not sure that it's accurately reported anywhere else, either (mostly because I don't use them). SIZE for a snapshot does not indicate the actual size of the snapshot. It indicates the maximum size that the snapshot can ever use.

The only way that I know of to get truly up to date file sizes is to make a call to the operating system with the understanding that the answer is only valid for that very instant and could change in the very next instant.