I'm working in SQL Server 2008 R2 and have created a query that gathers and sums the total of data files and log files' sizes. However, I can't find how much actual disk space a single block of SQL data takes up on the disk so I can convert it into something more meaningful.

Here is the script:

SELECT @DataSize = SUM(size) from sys.database_files where type_desc = 'Rows'
SELECT @LogSize = SUM(size) from sys.database_files where type_desc = 'Log'
PRINT @DataSize
PRINT @LogSize

How large is one block of space? Would it be easy to convert those two integer variables into something more meaningful for a sysadmin?


1 Answer 1


From the column documentation of sys.database_files:

size: Current size of the file, in 8-KB pages.

So a more meaningful query might be something like this:

    df.name AS LogicalFileName,
    df.physical_name AS PhysicalPath,
    CONVERT(int, (((df.size * 8192) / 1024.0) / 1024.0)) AS SizeInMB
    FROM sys.database_files df;

(You can expand on that further as necessary.)

  • 2
    It's worth adding that SQL Server does data file I/O in chunks of 8 pages, or 64 KB "extents", so optimize storage around that rather than individual 8 KB pages.
    – db2
    Jul 9, 2013 at 20:51
  • Additionally, if the disk is dedicated to SQL Server data files then format it using an 8192 byte allocation unit. Jul 10, 2013 at 4:02
  • Actually no - a 8192yte allocation unit would be utterly stupid. As SQL Server does all IO in extends - you need to format it witha 64k allocation unit. Then NTFS allocation units and IO operations line up. SQL Server never does smaller IO than 64k.
    – TomTom
    Jul 10, 2013 at 4:03
  • 9
    @TomTom Actually no, SQL Server does not do all IO in extents. The tests demonstrated in Dissecting SQL Server physical reads with Extended Events and Process monitor and Detailed Windows I/O: Process Monitor are easily replicated (and interesting to observe). Jul 10, 2013 at 11:07
  • 7
    That said, 64k allocation is still the recommendation although this typically has no effect on performance (IOPS, MB/sec or latency). Jul 10, 2013 at 11:08

Your Answer

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

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