Gathering Information About Read/Write Errors

The sp_diskblock procedure shown below translates a SQL Server virtual disk and block number into the corresponding SQL Server device, database, and logical page number. This information can be useful for gathering information about read or write errors that SQL Server might encounter, such as the Read/Write Error described in Chapter 26, "System Messages."

Because the sp_diskblock procedure collects information from the system tables of the SQL Server on which it is executed, you must execute sp_diskblock on the SQL Server where the read/write error occurred.

Syntax

sp_diskblock virtual_disk, block_number

Example

sp_diskblock 0, 22
  
Virtual disk 0, block 22 corresponds to:
Logical page 18 in the "master" database
(dbid=1) on device "master".
  

Stored Procedure Code

CREATE PROC sp_diskblock @@disk int, @@block int AS
DECLARE @@low    int,
        @@dname  varchar(30),
        @@msg    varchar(90),
        @@lpage  int,
        @@dbid   int,
        @@segmap int
  
SELECT  @@low = low,  @@dname = name
    FROM master.dbo.sysdevices WHERE low/16777216 = @@disk
  
IF (@@low IS NULL)
    BEGIN
        SELECT @@msg = 'Virtual device '  CONVERT(varchar, @@disk)
          ' does not exist on this server.'
        PRINT @@msg
        RETURN (1)
    END
ELSE
    BEGIN
        SELECT  @@lpage = lstart  @@block  @@low - vstart,
            @@dbid = dbid, @@segmap = segmap
            FROM master.dbo.sysusages WHERE (@@block  @@low) >= vstart
            AND (@@block  @@low) <= (vstart  size)
        IF (@@dbid IS NULL)
            BEGIN
                SELECT @@msg = 'Block '  CONVERT(varchar, @@block)
                    ' on disk "'  @@dname
                     '" is currently not in use for any database.'
                PRINT @@msg
                RETURN (1)
            END
        ELSE
            BEGIN
                SELECT @@msg = "Virtual disk "  convert(varchar,@@disk)
                     ", block "  convert(varchar,@@block)
                     " corresponds to:"
                PRINT @@msg
                SELECT @@msg ='Logical page '  convert(varchar,@@lpage)
                     ' in the "'   DB_NAME(@@dbid)
                     '" database (dbid='   convert(varchar(3),@@dbid)
                     ') on device "'  @@dname  '".'
                PRINT @@msg
            END
    END
RETURN (0)
  
go