Simon's SQL

First of all, let’s take a look system view

sp_helptext ‘sys.sysfiles’
sp_helptext ‘master.dbo.sysaltfiles’

<Sys.sysfiles – SQL2014>

CREATE VIEW sys.sysfiles AS
SELECT
fileid = convert(smallint, fileid & 0x7fff),
groupid = convert(smallint, grpid),
size = isnull(FilePropertyById(fileid, ‘size’), size),
maxsize, growth,
status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
name = lname,
filename = pname
FROM sys.sysprufiles
WHERE filetype IN (0, 1) — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)
AND filestate NOT IN (1, 2, 3) — x_efs_Dummy, x_efs_Dropped, x_efs_DroppedReusePending

<master.dbo.sysaltfiles – SQL2014>

CREATE VIEW sys.sysaltfiles AS
SELECT fileid = convert(smallint, f.fileid & 0x7fff),
groupid = convert(smallint, f.grpid),
f.size, f.maxsize, f.growth,
status = convert(int,
case f.filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (f.status & 8) * 2 — FCB_READONLY_MEDIA
+ (f. status & 16) * 256 — FCB_READONLY
+ case when f.filestate in (1, 2, 3, 6) then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (f.status & 256) * 2097152 — FCB_SPARSE_FILE
+ (f.status & 32) * 32768), — FCB_PERCENT_GROWTH
perf = convert(int, 0),
dbid = convert(smallint, f.dbid),
name = f.lname, filename = f.pname
FROM master.sys.sysbrickfiles f
WHERE f.filetype IN (0, 1) AND has_access(‘MF’, 1) = 1 — x_eft_SQLData, x_eft_SQLLog (bwkcmpt types)

You can find out the source code looks like below for status

status = convert(int,
case filetype when 1 then 66 else 2 end — x_eft_SQLLog, FCB_LOG_DEVICE, FCB_DSK_DEVICE
+ (status & 8) * 2 — FCB_READONLY_MEDIA
+ (status & 16) * 256 — FCB_READONLY
+ case filestate when 6 then 268435456 else 0 end — OFFLINE, FCB_OFFLINE
+ (status & 256) * 2097152 — FCB_SPARSE_FILE
+ (status & 32) * 32768), — FCB_PERCENT_GROWTH

This is bit operation. So, it need to convert back using same operation.

SELECT *
, status
, status & 2 AS [FCB_DSK_DEVICE]
, status & 66 AS [FCB_LOG_DEVICE]
, status & (8 * 2) AS [FCB_READONLY_MEDIA]
, status & (16 * 256) AS [FCB_READONLY]
, status & (268435456) AS [OFFLINE, FCB_OFFLINE]
, status & (256 * 2097152) AS [FCB_SPARSE_FILE]
, status & (32 * 32768) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

This query equivalent with below query

/*
SELECT CONVERT(VARBINARY(8), 2)
SELECT CONVERT(VARBINARY(8), 66)
SELECT CONVERT(VARBINARY(8), (8 * 2))
SELECT CONVERT(VARBINARY(8), (16 * 256))
SELECT CONVERT(VARBINARY(8), 268435456)
SELECT CONVERT(VARBINARY(8), (256 * 2097152))
SELECT CONVERT(VARBINARY(8), (32 * 32768))
*/
SELECT *
, status
, status & 0x2 AS [FCB_DSK_DEVICE]
, status & 0x42 AS [FCB_LOG_DEVICE]
, status & 0x10 AS [FCB_READONLY_MEDIA]
, status & 0x1000 AS [FCB_READONLY]
, status & 0x10000000 AS [OFFLINE, FCB_OFFLINE]
, status & 0x20000000 AS [FCB_SPARSE_FILE]
, status & 0x100000 AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles

BOL saying “0x40 = Log file.” since this is bit operation.

I realized it. It’s due to “FCB_DSK_DEVICE” included. So, 0x40+2 = 0x42.

So, 0x40 is for FCB_LOG_DEVICE

https://msdn.microsoft.com/en-us/library/ms178009.aspx

Final query

SELECT *
, status
, CONVERT(BIT, status & 0x2) AS [FCB_DSK_DEVICE]
, CONVERT(BIT, status & 0x40) AS [FCB_LOG_DEVICE]
, CONVERT(BIT, status & 0x10) AS [FCB_READONLY_MEDIA]
, CONVERT(BIT, status & 0x1000) AS [FCB_READONLY]
, CONVERT(BIT, status & 0x10000000) AS [OFFLINE, FCB_OFFLINE]
, CONVERT(BIT, status & 0x20000000) AS [FCB_SPARSE_FILE]
, CONVERT(BIT, status & 0x100000) AS [FCB_PERCENT_GROWTH]
FROM master..sysaltfiles