Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

understading status column of sysfiles and sysaltfiles view

Posted by Simon Cho on 01/04/2016

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

 

 

 

One Response to “understading status column of sysfiles and sysaltfiles view”

  1. Anonymous said

    Beautiful stuff. We dbas are way out of touch with the underlying file system.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s