INF: Determining User Segments from Segmap Values

Last reviewed: April 28, 1997
Article ID: Q101241

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

This article describes a stored procedure which can be used to determine which user segments map to a Microsoft SQL Server device based on the values from the master.dbo.sysusages segmap column.

MORE INFORMATION

One difficulty in administering databases in which user segments have been defined is determining which segments map to each database device.

Each user segment in a database is assigned a segment number unique in that database, this number is recorded in the dbo.syssegments table in each database. The master.dbo.sysusages table indicates how each database maps to its devices. The segmap column in the sysusages table holds the information about which segments map onto each device.

The stored procedure has been kept simple so that users can run it even in new systems to which they have not yet migrated user databases. The syntax is:

   execute sp_segmap <segmap value>

sp_segmap returns a column segment_num which contains segment numbers which will match the column segment in the table dbo.syssegments in the user database.

Example

For an example of sp_segmap's use, assume that a database has one line in sysusages which has a segmap value of 7.

   execute sp_segmap 7

returns segment_num values of 0, 1, and 2. Looking in the syssegments table for that database will show that segments 0, 1, and 2 are the system, default, and logsegment segments.

Type the following into a file segmap.sql:

use master go if exists (select * from sysobjects

                where name = 'sp_segmap')
  drop procedure sp_segmap
go

create procedure sp_segmap @segmap int as

create table #seg

      (segment_num int NULL)

declare @segnum int declare @pattern int select @segnum = 0 select @pattern = 1

if @segmap = 0

/* no segments mapped */
begin
  insert #seg values(NULL)
  select * from #seg
  order by segment_num
  return
end

while (@segnum < 30) begin

  if (@segmap & @pattern) != 0
    insert #seg values(@segnum)
  select @pattern = @pattern * 2
  select @segnum = @segnum + 1
end

/* Test final bit in @pattern */
if (@segmap & @pattern) != 0
  insert #seg values(@segnum)

/* Test if segment 31 bit on */
if @segmap <= -1
  insert #seg values(31)

select * from #seg order by segment_num go

grant execute on sp_segmap to public go

Run segmap.sql in isql as the sa user:

isql /Usa /P<password> /S<server> /e /isegmap.sql /osegmap.sql


Additional query words: segments Transact_SQL Windows NT
Keywords : kbprg SSrvAdmin SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.