Before You Create and Execute sp_diskblock

Before creating and executing sp_diskblock, note the following:

Create sp_diskblock in the sybsystemprocs database.
If you change the name of the procedure, make sure the new procedure name begins with “sp_”.
Review the Transact-SQL User's Guide explanation of how to create and execute stored procedures.

Syntax

sp_diskblock virtual_disk, block_number

Sample

1> sp_diskblock 4, 871
2> go

Virtual disk 4, block 871 corresponds to:
Logical page 1895 in the "production" database
(dbid=4) on device "main".

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
     and cntrltype = 0
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)