Tag Archives: Sybase ASE

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

Ed Barlow Stored Procedures: sp__monobj Updated with formatting for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__monobj uses a heuristic algorithm to find which objects are most busy as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output

Differences:

diff sp__monobj.old sp__monobj.15
17a18,26
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Fix formatting of outputs
> -- 1.0 |  2006    |  Edward Barlow     |    | Shows Highest Usage Objects Based On Mda tables.
> --     |          |                    |    |  This uses a heuristic algorithm to find what objects
> --     |          |                    |    |  are most busy.
> -------+----------+--------------------+----+-----------------------------------------------------
>
21a31,34
> declare @max_objectname_size varchar(3)
> declare @exec_str varchar(2000)
>
>
34a48,51
>     print ""
>     print "    score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits"
>     print ""
>
36,37c53,55
< select ObjName=convert(varchar(40),db_name(DBID)+"."+object_name(ObjectID,DBID)),
<       score= LogicalReads/100 +
---
>
>       select ObjName = db_name(DBID) + ".." + object_name(ObjectID, DBID),
>       score = LogicalReads/100        +
45,46c63,65
< from    master..monOpenObjectActivity
<       where   IndexID=0
---
>     into #busy_report
>       from master..monOpenObjectActivity
>       where IndexID=0
55c74
< and     db_name(DBID)!="tempdb"
---
>       and     DBName !="tempdb"
57a77
>
58a79,87
>
>     select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report
>
>     select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName), score
>         from #busy_report
>         order by score desc'
>
>     exec (@exec_str)
>
72a102,121
>     create table #busy_report_iter_obj (
>         ObjName varchar(255) not null,
>         Op bigint not null,
>         LogReads int not null,
>         PhysReads int not null,
>         PageReads int not null,
>         Writes int not null,
>         Ins int not null,
>         Del int not null,
>         Upd int not null,
>         Locks int not null,
>         LockWt int not null)
>
>     create table #busy_report_iter (
>         ObjName varchar(255) not null,
>         Op int not null,
>         Reads int not null,
>         Writes int not null,
>         NumRows bigint not null)
>
81,86c130,131
< -- select *
<                       -- from master..monOpenObjectActivity
<                       -- where IndexID=0 and db_name(DBID)!='tempdb'
<                       -- and ( @object_name is null or @object_name=object_name(ObjectID, DBID))
<
<               select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)),
---
>             insert into #busy_report_iter_obj
>                   select distinct ObjName = db_name(o.DBID) + ".." + object_name(o.ObjectID,o.DBID),
88c133
< LogReads=o.LogicalReads - i.LogicalReads,
---
>                           LogReads=o.LogicalReads - i.LogicalReads,
92,103c137,167
< "Ins"=o.RowsInserted -i.RowsInserted,
<                               "Del"= o.RowsDeleted  - i.RowsDeleted,
<                               "Upd"= o.RowsUpdated - i.RowsUpdated ,
<                               "Locks"= o.LockRequests - i.LockRequests,
<                               "LockWt"= o.LockWaits - i.LockWaits
<               from  master..monOpenObjectActivity o,#tmp i
<               where o.IndexID=i.IndexID
<                       and o.ObjectID=i.ObjectID
<                       and o.DBID=i.DBID
<                       and o.IndexID=i.IndexID
<                       and o.IndexID=0
<                       and i.IndexID=0
---
>                           Ins=o.RowsInserted -i.RowsInserted,
>                               Del= o.RowsDeleted  - i.RowsDeleted,
>                               Upd= o.RowsUpdated - i.RowsUpdated ,
>                               Locks= o.LockRequests - i.LockRequests,
>                               LockWt= o.LockWaits - i.LockWaits
>                   from  master..monOpenObjectActivity o,#tmp i
>                   where o.IndexID=i.IndexID
>                           and o.ObjectID=i.ObjectID
>                           and o.DBID=i.DBID
>                           and o.IndexID=i.IndexID
>                           and o.IndexID=0
>                           and i.IndexID=0
>
>             select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj
>
>             select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName),
>                               Op,
>                           LogReads,
>                               PhysReads,
>                               PageReads,
>                               Writes,
>                           Ins,
>                               Del,
>                               Upd,
>                               Locks,
>                               LockWt
>                 from #busy_report_iter_obj'
>
>             exec (@exec_str)
>
>             delete #busy_report_iter_obj
107c171
< set rowcount 10
---
>                   set rowcount 10
109,138c173,202
< select distinct ObjName=convert(varchar(39),db_name(o.DBID)+"."+object_name(o.ObjectID,o.DBID)),
<               Op=o.Operations-i.Operations,
<       Reads=o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
<               Writes=o.PhysicalWrites-i.PhysicalWrites,
<       "Rows"=o.RowsInserted + o.RowsDeleted   + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated
<       from  master..monOpenObjectActivity o,#tmp i
<    where    o.IndexID=i.IndexID
<               and o.ObjectID=i.ObjectID
<               and o.DBID=i.DBID
<               and o.IndexID=i.IndexID
<               and o.IndexID=0
<               and i.IndexID=0
<               -- and ( o.LogicalReads>0 or o.LockRequests>0 )
< order by
<       o.LogicalReads/100      +
<       o.PhysicalReads/10      +
<       o.PhysicalWrites                +
<       o.RowsInserted                  +
<       o.RowsDeleted                   +
<       o.RowsUpdated                   +
<       o.LockRequests/100   +
<       o.LockWaits             -
<       i.LogicalReads/100      -
<       i.PhysicalReads/10      -
<       i.PhysicalWrites                -
<       i.RowsInserted                  -
<       i.RowsDeleted                   -
<       i.RowsUpdated                   -
<       i.LockRequests/100   -
<       i.LockWaits    desc
---
>             insert into #busy_report_iter
>             select distinct ObjName = db_name(o.DBID) + ".." + object_name(o.ObjectID,o.DBID),
>                       Op = o.Operations-i.Operations,
>                 Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
>                       Writes = o.PhysicalWrites-i.PhysicalWrites,
>                 NumRows = o.RowsInserted + o.RowsDeleted   + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated
>             from  master..monOpenObjectActivity o,#tmp i
>             where    o.IndexID=i.IndexID
>                       and o.ObjectID=i.ObjectID
>                       and o.DBID=i.DBID
>                       and o.IndexID=i.IndexID
>                       and o.IndexID=0
>                       and i.IndexID=0
>             order by
>                   o.LogicalReads/100  +
>                 o.PhysicalReads/10    +
>                 o.PhysicalWrites              +
>                 o.RowsInserted                +
>                 o.RowsDeleted                 +
>                 o.RowsUpdated                 +
>                 o.LockRequests/100   +
>                 o.LockWaits                   -
>                   i.LogicalReads/100  -
>                 i.PhysicalReads/10    -
>                 i.PhysicalWrites              -
>                 i.RowsInserted                -
>                 i.RowsDeleted                 -
>                 i.RowsUpdated                 -
>                 i.LockRequests/100   -
>                 i.LockWaits    desc
139a204,215
>             select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter
>
>             select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName),
>                 Op,
>                 Reads,
>                 Writes,
>                 NumRows
>             from #busy_report_iter'
>
>             exec (@exec_str)
>
>             delete #busy_report_iter
152d227
< -- select 1,* from #tmp where object_id('sp_aux_getsize')=ObjectID and DBID=db_id('master')

SQL of sp__monobj:

use sybsystemprocs
go
/* Procedure library copyright(c) 2004-2006 by Edward M Barlow */

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monobj"
           AND    type = "P")
   DROP PROC sp__monobj

go

CREATE PROC sp__monobj(
                @num_sec_delay int=NULL,
                @num_iter int=NULL,
                @dont_format char(1)=NULL,
                @object_name varchar(30)=NULL)
AS
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/20/2013|  Jason Froebe      |    | Fix formatting of outputs
-- 1.0 |  2006    |  Edward Barlow     |    | Shows Highest Usage Objects Based On Mda tables.
--     |          |                    |    |  This uses a heuristic algorithm to find what objects
--     |          |                    |    |  are most busy.
-------+----------+--------------------+----+-----------------------------------------------------

set nocount on
declare @delay char(8)
declare @objid int

declare @max_objectname_size varchar(3)
declare @exec_str varchar(2000)


if @object_name is not null
begin
        select @objid=ObjectID
        from master..monOpenObjectActivity where object_name(ObjectID,DBID)=@object_name
        if @objid is null
                return
end


if @num_sec_delay is null
begin
        --Busy Object Report
        print "Top 20 Used Objects"
    print ""
    print "    score = LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits"
    print ""

        set rowcount 20

        select ObjName = db_name(DBID) + ".." + object_name(ObjectID, DBID),
        score = LogicalReads/100        +
                PhysicalReads/10 +
                PhysicalWrites +
                RowsInserted   +
                RowsDeleted    +
                RowsUpdated    +
                LockRequests   +
                LockWaits
    into #busy_report
        from master..monOpenObjectActivity
        where IndexID=0
        and LogicalReads/100    +
                PhysicalReads/10 +
                PhysicalWrites +
                RowsInserted   +
                RowsDeleted    +
                RowsUpdated    +
                LockRequests   +
                LockWaits      >1000
        and     DBName !="tempdb"
        and ( @object_name is null or @object_name=object_name(ObjectID, DBID))
        order by score desc

        set rowcount 0

    select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report

    select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName), score
        from #busy_report
        order by score desc'

    exec (@exec_str)

end
else
begin
        select *
        into #tmp
        from master..monOpenObjectActivity
        where IndexID=0 and db_name(DBID)!='tempdb'
        and ( @object_name is null or ObjectID=@objid )

        if @num_sec_delay<10
                select @delay="00:00:0"+convert(char(1),@num_sec_delay)
        else
                select @delay="00:00:"+convert(char(2),@num_sec_delay)

    create table #busy_report_iter_obj (
        ObjName varchar(255) not null,
        Op bigint not null,
        LogReads int not null,
        PhysReads int not null,
        PageReads int not null,
        Writes int not null,
        Ins int not null,
        Del int not null,
        Upd int not null,
        Locks int not null,
        LockWt int not null)

    create table #busy_report_iter (
        ObjName varchar(255) not null,
        Op int not null,
        Reads int not null,
        Writes int not null,
        NumRows bigint not null)

        if @num_iter is null
                select @num_iter=100
        while @num_iter>0
        begin
                waitfor delay @delay

                if( @object_name is not null )
                begin
            insert into #busy_report_iter_obj
                    select distinct ObjName = db_name(o.DBID) + ".." + object_name(o.ObjectID,o.DBID),
                                Op=o.Operations-i.Operations,
                    LogReads=o.LogicalReads - i.LogicalReads,
                                PhysReads=o.PhysicalReads- i.PhysicalReads,
                                PageReads=o.PagesRead- i.PagesRead,
                                Writes=o.PhysicalWrites-i.PhysicalWrites,
                    Ins=o.RowsInserted -i.RowsInserted,
                                Del= o.RowsDeleted  - i.RowsDeleted,
                                Upd= o.RowsUpdated - i.RowsUpdated ,
                                Locks= o.LockRequests - i.LockRequests,
                                LockWt= o.LockWaits - i.LockWaits
            from  master..monOpenObjectActivity o,#tmp i
                    where o.IndexID=i.IndexID
                            and o.ObjectID=i.ObjectID
                            and o.DBID=i.DBID
                            and o.IndexID=i.IndexID
                            and o.IndexID=0
                            and i.IndexID=0

            select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter_obj

            select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName),
                                Op,
                    LogReads,
                                PhysReads,
                                PageReads,
                                Writes,
                    Ins,
                                Del,
                                Upd,
                                Locks,
                                LockWt
                from #busy_report_iter_obj'

            exec (@exec_str)

            delete #busy_report_iter_obj
                end
                else
                begin
                    set rowcount 10

            insert into #busy_report_iter
            select distinct ObjName = db_name(o.DBID) + ".." + object_name(o.ObjectID,o.DBID),
                        Op = o.Operations-i.Operations,
                Reads = o.LogicalReads+ o.PhysicalReads- i.LogicalReads- i.PhysicalReads,
                        Writes = o.PhysicalWrites-i.PhysicalWrites,
                NumRows = o.RowsInserted + o.RowsDeleted   + o.RowsUpdated-i.RowsInserted - i.RowsDeleted - i.RowsUpdated
            from  master..monOpenObjectActivity o,#tmp i
            where    o.IndexID=i.IndexID
                        and o.ObjectID=i.ObjectID
                        and o.DBID=i.DBID
                        and o.IndexID=i.IndexID
                        and o.IndexID=0
                        and i.IndexID=0
            order by
                    o.LogicalReads/100  +
                o.PhysicalReads/10      +
                o.PhysicalWrites                +
                o.RowsInserted                  +
                o.RowsDeleted                   +
                o.RowsUpdated                   +
                o.LockRequests/100   +
                o.LockWaits             -
                    i.LogicalReads/100  -
                i.PhysicalReads/10      -
                i.PhysicalWrites                -
                i.RowsInserted                  -
                i.RowsDeleted                   -
                i.RowsUpdated                   -
                i.LockRequests/100   -
                i.LockWaits    desc

            select @max_objectname_size = convert(varchar(3), isnull( max(char_length(ObjName)), 1)) from #busy_report_iter

            select @exec_str = 'select "Object Name" = convert(varchar(' + @max_objectname_size + '), ObjName),
                Op,
                Reads,
                Writes,
                NumRows
            from #busy_report_iter'

            exec (@exec_str)

            delete #busy_report_iter
                set rowcount 0

                end

                delete #tmp

                insert #tmp
                select *
                from master..monOpenObjectActivity
                where IndexID=0 and db_name(DBID)!='tempdb'
                and ( @object_name is null or ObjectID=@objid )

                select @num_iter = @num_iter - 1
        end
end

return

go

GRANT EXECUTE ON sp__monobj  TO public
go

sp__monwaits

Share Button

Ed Barlow Stored Procedures: sp__monrunning updated with formatting for SAP Sybase ASE 15 and higher

Mich Talebzadeh createdSAP Sybase the sp__monrunning stored procedure for displaying the procedures that are running for more than 100 ms as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

I’ve cleaned up the format output and added the dont_format option:

$ diff sp__monrunning.old sp__monrunning.15
9c9,10
< create procedure sp__monrunning
---
> create procedure sp__monrunning(
>                      @dont_format char(1)=NULL)
11,16c12,18
< ------------------------------------------------------------------------------------------------
< -- Vers|  Date  |      Who           | DA | Description
< -------+--------+--------------------+----+-----------------------------------------------------
< -- 1.0 |07/04/06|  Mich Talebzadeh   |    | Statistics on processes currently being executed
< --     |        |                    |    | with Elapsed time > 100ms
< -------+--------+--------------------+----+-----------------------------------------------------
---
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
> -- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
> --     |          |                    |    | with Elapsed time > 100ms
> -------+--  ------+--------------------+----+-----------------------------------------------------
18,22c20,24
< declare @time datetime
<       select @time = getdate()
<       --print ""
<       --print "Stats for various procedures at %1!. Server up since %2!", @time, @@boottime
<       --print ""
---
>     declare @max_loginname_size varchar(3)
>     declare @max_procname_size varchar(3)
>     declare @max_dbname_size varchar(3)
>     declare @exec_str varchar(2000)
>
35c37
< "Name" = substring(suser_name(p.suid),1,20),
---
>               "Name" = suser_name(p.suid),
37,39c39,42
< "Procedure" = ProcName,
<               "Database" = DBNAME,
<               "Elapsed Time/ms" = TimeMs
---
>               ProcName,
>               DBNAME,
>               TimeMs
>     into #proc_report
43c46,75
< order by TimeMs asc
---
>
>     select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
>         @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
>         @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
>     from #proc_report
>
>     if @dont_format is null
>     begin
>         select @exec_str = 'select
>             "Name" = convert(varchar(' + @max_loginname_size + '), Name),
>                   SPID,
>                   "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
>                   "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc'
>
>         exec (@exec_str)
>     end
>     else
>     begin
>         select
>             "Name" = Name,
>                   SPID,
>                   "Procedure" = ProcName,
>                   "Database" = DBNAME,
>                   "Elapsed Time/ms" = TimeMs
>         from #proc_report
>           order by TimeMs asc
>     end

Full SQL code of sp__monrunning:

use sybsystemprocs
go

IF EXISTS (SELECT * FROM sysobjects
           WHERE  name = "sp__monrunning"
           AND    type = "P")
   DROP PROC sp__monrunning
go
create procedure sp__monrunning(
                     @dont_format char(1)=NULL)
as
--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/20/2013|  Jason Froebe      |    | Cleaned up the report format, added dont_format option
-- 1.0 |07/04/2006|  Mich Talebzadeh   |    | Statistics on processes currently being executed
--     |          |                    |    | with Elapsed time > 100ms
-------+--  ------+--------------------+----+-----------------------------------------------------
begin
    declare @max_loginname_size varchar(3)
    declare @max_procname_size varchar(3)
    declare @max_dbname_size varchar(3)
    declare @exec_str varchar(2000)

        select
                SPID,
                ProcName = isnull(object_name(ProcedureID, DBID),"UNKNOWN"),
                DBNAME = isnull(db_name(DBID), "UNKNOWN"),
                TimeMs = datediff(ms, min(StartTime), max(EndTime))
                into #performance
                from master..monSysStatement m
        where db_name(DBID) != 'sybsystemprocs'
        group by SPID, DBID, ProcedureID, BatchID
        having ProcedureID != 0

        select distinct
                "Name" = suser_name(p.suid),
                SPID,
                ProcName,
                DBNAME,
                TimeMs
    into #proc_report
        from #performance t, master..sysprocesses p
        where t.SPID = p.spid
        and TimeMs >= 100

    select @max_loginname_size = convert(varchar(3), isnull( max(char_length(Name)), 1)),
        @max_procname_size = convert(varchar(3), isnull( max(char_length(ProcName)), 1)),
        @max_dbname_size = convert(varchar(3), isnull( max(char_length(DBNAME)), 1))
    from #proc_report

    if @dont_format is null
    begin
        select @exec_str = 'select
            "Name" = convert(varchar(' + @max_loginname_size + '), Name),
                    SPID,
                    "Procedure" = convert(varchar(' + @max_procname_size + '), ProcName),
                    "Database" = convert(varchar(' + @max_dbname_size + '), DBNAME),
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc'

        exec (@exec_str)
    end
    else
    begin
        select
            "Name" = Name,
                    SPID,
                    "Procedure" = ProcName,
                    "Database" = DBNAME,
                    "Elapsed Time/ms" = TimeMs
        from #proc_report
            order by TimeMs asc
    end
end
go
grant exec on sp__monrunning to public
go
exit

sp__monrunning

Share Button