Tag Archives: Sybase ASE

A fast method of getting space utilization: sp__quickdbspace for SAP Sybase ASE

The sp__quickdbspace procedure relies on accurate metrics provided by free space accounting and/or dbcc checkalloc/tablealloc. It shouldn’t be relied upon on for exact space usage.

Much of the code comes from sp_helpdb from Sybase. er.. SAP.

sp__quickdbspace output:


1>  sp__quickdbspace mydb
2> go
 DBName               data size MB       data free MB       log free MB
 -------------------- ------------------ ------------------ ------------------
 mydb                         5025662.00          703975.00          186260.00
(return status = 0)
1>

sp__quickdbspace code:

if exists (select 1 from sysobjects where name = "sp__quickdbspace")
  drop procedure sp__quickdbspace
go

create procedure sp__quickdbspace 
@dbname varchar(255) = NULL
as
declare @numpgsmb float	/* Number of Pages per Megabyte */
declare @sptlang int
declare @log_free bigint

set nocount on

if @dbname = null
  select @dbname = db_name()

select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"	

select @sptlang = @@langid

if @@langid != 0
begin
  if not exists (select * from master.dbo.sysmessages where error between 17050 and 17069 and langid = @@langid)
    select @sptlang = 0
  else
    if not exists (select * from master.dbo.sysmessages where error between 17110 and 17119 and langid = @@langid)
      select @sptlang = 0
end

select 
  device_fragments = v.name,
  size = (size / @numpgsmb),
  usage = convert(char(30), m.description),
  created = convert(char(25), u.crdate, 100),
  case
    when u.segmap = 4 then null
    else (convert(bigint, curunreservedpgs(d.dbid, u.lstart, u.unreservedpgs)) / @numpgsmb)
  end "free_mb"
  into #device_usage
from master.dbo.sysdatabases d,
  master.dbo.sysusages u,
  master.dbo.sysdevices v,
  master.dbo.spt_values b,
  master.dbo.sysmessages m
where d.dbid = u.dbid
  and u.vdevno = v.vdevno
  and ((v.status & 2 = 2)  or (v.status2 & 8 = 8))
  and d.name = @dbname
  and b.type = 'S'
  and u.segmap & 7 = b.number
  and b.msgnum = m.error
  and isnull(m.langid, 0) = @sptlang
  
	if exists (select *
	    from master.dbo.sysdatabases d, master.dbo.sysusages u
		where d.name like @dbname
			and d.dbid = u.dbid
			and u.segmap = 4
			and (d.status & ( 32 + 64 + 128 + 256) = 0)
			and (d.status2 & 64) = 0)
	begin
		 select @log_free = (lct_admin("logsegment_freepages", db_id(@dbname) ) - lct_admin("reserved_for_rollbacks", db_id(@dbname))) / @numpgsmb
	end
    
    select convert(varchar(20), @dbname) as "DBName", convert(numeric(15,2), sum(size)) as "data size MB", convert(numeric(15,2), sum(free_mb)) as "data free MB", convert(numeric(15,2), @log_free) as "log free MB" from #device_usage
    
drop table #device_usage
go
Share Button

Drew Montgomery’s sp__dbsubspace for SAP Sybase ASE space usage

A few years back we were both working at a company that needed a method to obtain Sybase ASE database space usage every few minutes. The output of Ed Barlow’s sp__dbspace was fine but it was an expensive call. Drew Montgomery devised a set of stored procedures that would partially cache the results. Kudos to Drew!

With large databases, several of the calculations would overflow. I’m made only minor changes, mainly convert(numeric(19,5), … ) inside the sum()s.

sp__dbsubspace output:


1> sp__dbsubspace
2> go
 Name         Data MB       Used MB        Percent Log MB    Log Used  Log Pct Total MB
 ------------ ------------- -------------- ------- --------- --------- ------- ---------------
 mydb               4836323      4134753.0   85.49     87079  48793.15   56.03         4923402
(return status = 0)

sp_dbsubspace code:

/************************************************************************************************************************************************
 *  This process will use a persistant table called sysdbspaceinfo.  It is not a previously defined sybase system table, but  
 *  is used by the sp__dbsubspace procedure.  The database that this table resides in is defined by the "sybsystemdb" 
 *  marker.  Please replace the "sybsystemdb" marker with an actual database prior to installing this procedure.  In some    
 *  cases you may want to use something like sybsystemprocs or sybsystemdb.  You may also use tempdb, but if you do
 *  you may want to define the table in the model database as well, so it will exist when the server is restarted.  Good luck
 *  and enjoy.                                                                        Drew Montgomery
 ************************************************************************************************************************************************/

use master
go

/*  This is to set the Truncate Log on Checkpoint option for the database with the space check table on, so it doesn't fill up.
 *     This was done in case the database being used for this doesn't have its logs regularly cleaned (some sybase system
 *     databases are like that).  If the database you are going to use has a regular log cleanup procedure, you may comment 
 *     out this section.                         DM                                                                                                                       */
sp_dboption sybsystemdb, "trunc. log", true
go

use sybsystemdb
go

checkpoint
go

/* Drops the table if it already exists, as a precaution for the create */
if object_id('sysdbspaceinfo') is not null  
 drop table sysdbspaceinfo
go

create table sysdbspaceinfo 
   (dbid int, 
    DataPages numeric(19,5), 
    DataPagesUsed numeric(19,5), 
    LogPages numeric(19,5),
    LogPagesUsed numeric(19,5), 
    LogFirstPage int,
    NumberOfObjects int    )
go

/* Unique index is required for the isolation level, and makes the access faster! */
create unique index UCsysdbspaceinfo on sysdbspaceinfo(dbid)
go

/* Go to the sybsystemprocs to install the procedure */
use sybsystemprocs
go

/* Drop it first, as a precaution */
if object_id("sp__dbsubspace") is not null
  drop procedure sp__dbsubspace
go

create procedure sp__dbsubspace 
          @dont_format char(1) = NULL,          /* Flag for indicating the need to have more or less formatted information */
          @LogThreshold float = 0.01,              /* Change threshold for the amount of log "movement" before a recalculation of the data space is performed.
                                                                   The default value of 0.01 represents a 1.0% change in the amont of log space or the first page pointer of the log.
                                                                   Please note: it is theoretically possible that the log pointer could move just less than this amount and have 
                                                                   an amount of log space used that is just less than this amount, therefore it is POSSIBLE that change could be 
                                                                   two times this amount before a recalculation of the data space is performend.  Possible but not very likely.        */
           @Force varchar(10) = NULL
                                                                    
as
/* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 *  This procedure conceived and written by Drew Montgomery - please forward any and all substantial updates to 
 *        drew_montgomery@ameritech.net (so I can make this procedure better).  Thank you.                               
 * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

set nocount on
set lock nowait
set transaction isolation level 0

/* Declaration Section */
declare     @MixedFlag char,                                 /* Flag to signify if log and data are on the same device */
                @DataPages numeric(19,5),                  /* Number of data pages defined from sysusages */
                @LogPages numeric(19,5),                    /* Number of log pages defined from sysusages */
                @TotalPages numeric(19,5),                    /* Number of pages defined from sysusages */
                @DataPagesUsed numeric(19,5),           /* Number of data pages currently in use (reserved) */
                @LogPagesUsed numeric(19,5),             /* Number of log pages currently in use */
                @LogFirstPage bigint,                                /* First defined page of the transaction log */
                @NumberOfObjects int,                       /* for determining if a table has been dropped or added */
                @OldDataPages numeric(19,5),              /* Previous number of data pages defined from sysusages */
                @OldLogPages numeric(19,5),                /* Previous number of log pages defined from sysusages */
                @OldLogPagesUsed numeric(19,5),        /* Previous number of data pages being used */
                @OldDataPagesUsed numeric(19,5),       /* Previous number of log pages being used */
                @OldLogFirstPage int,                            /* Previous transaction log first page pointer */
                @OldNumberOfObjects int,                  /* Previous number of user tables in this database */
--                @LogDelta numeric(19,5),                       /* Change in the number of log pages being used */
--                @LFPDelta int,                                       /* Change in the location of the first log page */
                @scale numeric(19,5),                            /*  Multiplier used for displaying amount of space from number of pages -
                                                                                 this value is derived from the @@maxpagesize */
                @ReSize char,                                       /* Flag indicating if we need to recalculate the amount of data space being used */
                @pct_used numeric(10,2),                       /* Calculated percentage of data space used */
                @log_pct_used numeric(10,2)                  /* Calculated percentage of log space used */

set @ReSize = 'F'            /* Predefined as "No, thanks, we don't need to get a new reading of the data space being used -
                                          the old value will do just fine.   */

/* Determine the current data and log space allocated to this dabase as defined from sysusages */
select @DataPages = sum(case when segmap=4 then 0 else convert(numeric(19,5), size) end),
          @LogPages = sum(case when segmap & 4=4 then convert(numeric(19,5), size) else 0 end),
          @TotalPages = sum(convert(numeric(19,5), size))
  from master..sysusages where dbid = db_id()

/* If there are no log pages (or the value is null), then the system is defined as having Mixed data and log space -
 *   which also means that the number of potential log pages is the same as the number of data pages.              */
if isnull(@LogPages,0) = 0
begin
  set @MixedFlag = 'M'  -- Mode is Mixed
  set @LogPages=@DataPages
end
else if exists (select 1 from master..sysusages where dbid = db_id() and segmap &5 =5)
  set @MixedFlag = "C"  -- Stands for Confused
else
  set @MixedFlag = 'P'  -- Indicates Pristine

/* We are getting the number of pages being used by the syslogs table (table id = 8).
 * The first select line is "Prior to system 15 version", the second is the "System 15 or above" version-
 *   Please set the appropriate comment as to which kind of system is being used */
/*  select @LogPagesUsed = reserved_pgs(id, doampg), */
select @LogPagesUsed = reserved_pages(db_id(), id), 
          @LogFirstPage = first
  from sysindexes
 where id = 8

/* Extract the information from previous executions of sp__dbsubspace */
select @OldDataPages = DataPages, 
          @OldDataPagesUsed = DataPagesUsed,
          @OldLogPages = LogPages,
          @OldLogPagesUsed = LogPagesUsed,
          @OldLogFirstPage = LogFirstPage,
          @OldNumberOfObjects = NumberOfObjects
  from sybsystemdb..sysdbspaceinfo
where dbid = db_id()

/* Get an object count of the USER TABLES */
select @NumberOfObjects = count(*) from sysobjects where type = 'U'

/* If there are no records retrieved (first run) we need to Recalculate the size */
if @@rowcount = 0
begin
  set @ReSize = 'T'
end
else        
  if @OldDataPages != @DataPages           /* If the number of data pages changed from sysusages - Recalculate size */
  begin
    set @ReSize = 'T'
  end
  else
    if @OldLogPages != @LogPages            /* If the nubmer of log pages changed from sysusages - Recalculate size */
    begin
      set @ReSize = 'T'
    end
    else
    begin                                                   /* if the number of log pages used is greather than a percentage of the total number of log pages available - Recalc Size */
      if (@LogPagesUsed - @OldLogPagesUsed) > @LogThreshold * @LogPages
        set @ReSize = 'T'
      else
        begin                                               /* if the log's first page moved more than the threshold of the number of log pages available - Recalc Size */
        if (abs(@LogFirstPage - @OldLogFirstPage)) > @LogThreshold * @LogPages
          set @ReSize = 'T'
        else
          begin
            if (@OldNumberOfObjects != @NumberOfObjects)
              set @ReSize = 'T'
          end
        end
    end

if @Force is not null  -- This is a force option to make it update the value
  set @ReSize = 'T'

if @ReSize = 'T'                                        /* We are recalculating size, and getting back the new value of the Data Pages Used */
  exec sp__dbsubspace;2 @DataPages, @DataPagesUsed out, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects
else
begin                                                       /* or if we don't need to do that, we just use the previous value of the Data Pages Used */
  set @DataPagesUsed = @OldDataPagesUsed
end

/* Calculated scale from the maximum page size (size of the data pages, usually 2k, 4k, 8k, or 16k) */
set @scale = @@maxpagesize / 1024

/* "Borrowed" this calculation from sp__dbspace of the percentages used*/
if @MixedFlag = 'M' 
begin  /* Please note, if the mode is mixed, we will apparently have no Log Percentage Used as it is part of the data space */
  set @pct_used = convert(numeric(10,2), ((@DataPagesUsed + @LogPagesUsed) * 100) / @DataPages)
  set @log_pct_used = convert(numeric(10,2), 0)
end
else
begin
  set @pct_used = convert(numeric(10,2), (@DataPagesUsed * 100) / @DataPages) 
  set @log_pct_used = convert(numeric(10,2), (@LogPagesUsed * 100 )/(@LogPages) ) 
end

/* The @dont_format is from the sp__dbspace command - and provides the option for an abbreviated display of information */
if @dont_format is not null
begin            /* Provide the results based on the local variables - formatted first */
select  Name             = db_name(),
        "Data MB"  = str((@DataPages*@scale)/1024, 16, 0),
        "Used MB"  = str(((@DataPagesUsed + case @MixedFlag when 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 16, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB" = str((@LogPages*@scale)/1024, 12, 0),
        "Log Used"   = str(((case @MixedFlag when 'M' then 0 else @LogPagesUsed end)*@scale)/1024, 12, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 18, 0)
end
else
begin          /* And unformatted */
select  Name             = convert(char(12),db_name()),
        "Data MB"  = str((@DataPages*@scale)/1024, 13, 0),
        "Used MB"  = str(((@DataPagesUsed + case when @MixedFlag = 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 14, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB"   = str((@LogPages*@scale)/1024, 9, 0),
       "Log Used" = str(((case when @MixedFlag='M' then 0 else @LogPagesUsed end)*@scale)/1024, 9, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 15, 0)
end

/*  And Vola' we are done!  */
go

/* This is a subordinate procedure that gets the information from the sysindexes (or sysobjects for system 15+) and stores the information into the sysdbspaceinfo table */
create procedure sp__dbsubspace;2
  @DataPages numeric(19,5),                           /* See comments above about variables */
  @DataPagesUsed numeric(19,5) output,         /* NOTE: this value is returned to the calling procedure */
  @LogPages numeric(19,5),
  @LogPagesUsed numeric(19,5),
  @LogFirstPage bigint,
  @NumberOfObjects int

with recompile
as

  /* Prior to system 15 version */
  /* select @DataPagesUsed = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
     from sysindexes
    where id != 8
  */
/* System 15 and above version */    
   select @DataPagesUsed = sum(convert(numeric(19,5), reserved_pages(db_id(), id)))
     from sysobjects
    where id != 8
    
    
 /* Update the information in the table */
      update sybsystemdb..sysdbspaceinfo 
           set  DataPages = @DataPages,
                 DataPagesUsed = @DataPagesUsed,
                 LogPages = @LogPages,
                 LogPagesUsed = @LogPagesUsed,
                 LogFirstPage = @LogFirstPage,
                 NumberOfObjects = @NumberOfObjects
         where dbid = db_id()                 

/* if the update is not for any rows, then add a row */
        if @@rowcount = 0
          insert into sybsystemdb..sysdbspaceinfo values (db_id(), @DataPages, @DataPagesUsed, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects)
          
return  /* and we are done with this one */
go

/* You may want to grant some permissions (execute, perhaps) if the users are not sa's */
Share Button

SAP Sybase ASE Unable to shutdown with there are not enough ‘user connections’ Error: 1601, Severity: 17, State: 3

If you’re trying to shutdown ASE and you’re not able to log in to the ASE instance, you can shutdown the instance with “kill -15 ” on Linux/Unix:

server  Error: 1601, Severity: 17, State: 3
server  There are not enough 'user connections' available to start a new process. Retry when there are fewer active users, or ask your System Administrator to reconfigure ASE with more user connections

Obtain the os PID simply by running showserver:

$ showserver
USER          PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
sybase    542123 15.1  1.0 52220 93356  pts/2 A    11:15:35  8:34 /sybase/ASE-15_0/bin/dataserver -d/dev/rmasterd001 -e/sybase/ASE-15_0/install/errorlog -c/sybase/ASE-15_0/sybase.cfg -isybase -ssybase -M/sybase/mem 

Kill the dataserver process with “kill -15″ triggering a “shutdown with nowait” within ASE:

$ kill -15 542123

Only as a last resort, use “kill -9″.

  • If you need to, verify with “ipcs -m” that the shared memory segments are released, if not use “ipcrm” to release it.
  • Verify with “netstat -an |grep ” that the bound port(s) that ASE uses are released. If not, you may need to restart the machine to release them.
Share Button

SAP Sybase ASE: Unable to start ASE when getting “No unlocked logins with permission ‘…’ were found”? SOLVED

Have you received SAP Sybasethe error “No unlocked logins with permission ‘…’ were found” when attempting to start ASE?

00:0002:00000:00001:2014/03/14 08:51:04.87 server  Database 'master' is now online.
server  The transaction log in the database 'master' will use I/O size of 2 Kb.
server  No unlocked logins with permission '200' were found.
kernel  ueshutdown: exiting
kernel  Main thread performing final shutdown.
kernel  Network and device connection limit is 65519.
kernel  Encryption provider initialization succeeded on engine 1.
kernel  Thread 3 (LWP 41877545) of Threadpool syb_default_pool online as engine 1
kernel  Blocking call queue shutdown.
08:51:05.03 kernel  SySAM: Checked in license for 2 ASE_CORE

This message is the result of specifying the “-n” in the RUN server file for ASE.

-n system_privilege, –permission-logins – specifies the system privilege name so that a list of login accounts with this system privilege is printed into log file. – From Logging in to a locked-out Adaptive Server

When the “-n” is specified, it acts very similar to “-p” in the sense that ASE will make the change and shutdown. In this case, it would print the logins that have the system privilege of “200” then shutdown.

Share Button

SAP Sybase ASE 15.7: Unquiesce / quiesce not working

Typically there is a set of systems thatSAP are flashed from one system to another. The basic operation is: quiesce the databases in the source Sybase ASE instance, make a copy of the disk volume groups at the SAN level, move that volume group copy to the destination system, vary on the devices, start Sybase ASE and unquiesce the databases.

A set of devices were added to the source system but the volume group definition on the destination system wasn’t updated so a number of (raw) devices weren’t available to the destination Sybase ASE instance. Well, Sybase ASE did complain that the devices weren’t available but the automated unquiesce job called quiesece database.. release anyways. So it should have reported an error and done nothing further… right? Nope, the quiesce command removed the quiesce tag that is used to release the quiesce.

The destination volume group was refreshed and the devices were made available.

sp_helpdb showed:

1> sp_helpdb
2> go
 name           db_size       owner dbid  created      durability  lobcomplvl inrowlen status                                                               
 -------------- ------------- ----- ----- ------------ ----------- ---------- -------- -------
....
 goober_db          456.0 MB sa       13 Apr 30, 2012 full              NULL     NULL offline, quiesce database
....

Can we online it? Nope:

1>online database goober_db
2> go
Msg 921, Level 14, State 1:
Server 'super_duper_db_of_doom', Line 1:
Database 'goober_db' has not been recovered yet - please wait and try again.

Let’s try to find out what the quiesce tag is. We can check in monOpenDatabases or in dbcc resource. monOpenDatabases is a whole lot easier to read:

1> select DBID, QuiesceTag from master..monOpenDatabases where DBID = 13
2> go
 DBID        QuiesceTag
 ----------- ------------------------------
          13 NULL

No luck there, so let’s see what dbcc resource says that the status is. Lots and lots of output but it says that the database is recovering under spid 18:

1>dbcc traceon(3604)
2>go
1>dbcc resource
2>go
....
dbid = 13
Parallel thread spid: 18
Status: 0x30 ((0x00000020 (REC_ITEM_ONL_IMMEDIATELY), 0x00000010 (REC_ITEM_RECOVERING)))
....

I didn’t see spid 18 about in sysprocesses but that isn’t a guarantee that it isn’t in some state of release. Let’s kick out a stacktrace for this spid just in case. I would have been surprised if there was a stacktrace:

1> dbcc stacktrace(18)
2> go
Msg 3659, Level 16, State 3:
Server 'super_duper_db_of_doom', Line 1:
The spid 18 does not exist.

At this point, I changed the status and status2 columns of master..sysdatabases to 0 and restarted the instance. At worst, it should put the database into suspect mode. Well, the database was back in not recovered / recovering with quiesce mode.

Thinking I might have to reflash the set of devices I rebooted the database thinking perhaps we can have it rescan the dbtable page (or is it dbinfo? After nearly twenty years I still get the two names mixed up)

1>dbcc dbreboot(reboot, goober_db)
2>go

---------- Shutting Down Database 'goober_db' ----------
---------- Re-starting Database 'goober_db' With Recovery ----------
Recovering database 'goober_db'.
Started estimating recovery log boundaries for database 'goober_db'.
Database 'goober_db', checkpoint=(2154102095, 15), first=(2154102095, 15), last=(2154102095, 15).
Completed estimating recovery log boundaries for database 'goober_db'.
Started ANALYSIS pass for database 'goober_db'.
Completed ANALYSIS pass for database 'goober_db'.
Started REDO pass for database 'goober_db'. The total number of log records to process is 1.
Completed REDO pass for database 'goober_db'.
Recovery of database 'goober_db' will undo incomplete nested top actions.
Started filling free space info for database 'goober_db'.
Completed filling free space info for database 'goober_db'.
Started cleaning up the default data cache for database 'goober_db'.
Completed cleaning up the default data cache for database 'goober_db'.
Recovery complete.
Database 'goober_db' is now online.
---------- Operation on Database 'goober_db' Completed Successfully ----------

What??!? It’s online?

1> sp_helpdb
2> go
 name           db_size       owner dbid  created      durability  lobcomplvl inrowlen status                                                               
 -------------- ------------- ----- ----- ------------ ----------- ---------- -------- -------------------------------------------------------------------------------------------
....
 goober_db          456.0 MB sa       13 Apr 30, 2012 full              NULL     NULL no options set                    
....

Obviously dbcc reboot is doing something different in prep for recovery that restarting the instance does. My guess is that the dbtable is examined and updated/refreshed with the dbreboot dbcc where it is not for a reboot. I’ve opened a ticket with SAP about this. I’ll update this when some sort of answer is available

Share Button