Managing Direct Connections (kill and suspend)

[MichaelParma1]

ESRI has increasingly pointed users to leverage direct connects to SDE instead of application connections. We have followed this model and overall performance of our system is good. However, I have more and more often run into problems with users locking feature classes which we need to perform maintenance on.

I'm looking for a method to kill all user direct connections and suspend any new connections during the maintenance window. "sdemon -o kill ..." and "sdemon -o pause ..." don't work on the direct connects. Anyone have a suggestion or better yet, code examples?

Thanks,
Mike Parma
GIS Coordinator, City of New Braunfels

[DerekLaw]

Hi Mike,

Please review this Knowledge Base article:

KB #35889 - How To:  Kill direct connect processes to the ArcSDE database

There's also a Dev Sample available,

Kill Direct Connect

Hope this helps,

[RobertHu]

Hi Mike,

Are you using ArcSDE 10? In 10, you are supposed to be able to kill direct connections easily. Say, if you use SQL Server, you can do it like this,

sdemon -o kill -t all -i <direct connection info> ....

If you use sde schema, you must grant sde user more privilege - processadmin server role.

All these are in the new ArcSDE Administration Command Reference.

Thanks!

[DerekLaw]

Hi Mike,

Adding onto what Robert posted, here's the help documentation he mentions:

ArcSDE 10 Administration Command help - sdemon
(look under the Remove connections from a geodatabase section)

Hope this helps,

[BrentPierce]

Hi Mike,

We are also working on a project for our next release which concentrates on providing better tools for the Enterprise Geodatabase administrator. More specifically, we are going to allow administrators to see and disconnect connected users (both Direct Connect and Application Server) and also see what users currently have locks in the system. This will all be provided through a user interface in ArcGIS as well as scripting options provided through Python.

Hope this helps,
-Brent

[JonHall]

...providing better tools for the Enterprise Geodatabase administrator

That's GREAT, Brent!
I hope there's genuine interest at ESRI in accomplishing this through the ArcGIS User Interface, instead of through SQL Server Management Studio.

Along with viewing application connections and direct connections, their Locks, and ability to Kill connections, may I request:

A States viewer - I don't need the whole GDBT toolset, although the graphic tree of States was wortha thousand words, explaining to Users why they need to reconcil-post-delete versions and Compress sometimes 2 or 3 times a day, when generating thousands of transactions per hour from multiple users in versioned editing...

A Compress log viewer - Compress tools report "success" when they finish without errors, even if it didn't get down to 1 StateID - I want my lowly GIS Techs to know, did they fully compress, or not, without opening SSMS.

A Replica Log viewer - OK, maybe we don't need that anymore, if the Arc10 Replica Log viewer in Replica Manager finally removed the damn filter that wouldn't show anything older than 3 weeks, unless I opened SSMS.

Many thanks to all of you on ESRI's "Geodatabase & ArcSDE" team!

[MichaelParma1]

Derek,
I saw that article. However, I'm trying to script the entire thing or be able to run it from a tool. I need to be able to kill direct connects without logging in to the RDBMS.

Brent's comment sounds promising, as does the migration to 10. Due to integration issues we won't be able to move our SDE to 10 for about 6 more months. 😕

Thanks for the responses.
-Mike

[H__KorayGUNDUZ]

deploy to Oracle Users

Oracle Real Application Cluster (RAC) Users run the node 1 and node 2...


sqlplus sys/password@sidname as sysdba;


SQL>exec SYS.ARCSDE_USER_KILL;

or

SQL>exec ARCSDE_USER_KILL;

===========================================================

CREATE OR REPLACE PROCEDURE SYS."ARCSDE_USER_KILL"
/*************************************************
*Koray GUNDUZ (Ibb Cbs) - 2010
*Procedure kills all sessions listed in the sde.process_information table
*************************************************/
AS
SQL_STMT VARCHAR2(200);
CURSOR SDE_USERS IS
SELECT SERVER_ID,OWNER FROM SDE.PROCESS_INFORMATION WHERE DIRECT_CONNECT = 'Y';
NEWC INTEGER;
BEGIN
    FOR SDEREC IN SDE_USERS LOOP
        DECLARE CURSOR KILL_DC IS
        SELECT SID,SERIAL# FROM SYS.V$SESSION
        WHERE PROCESS LIKE SDEREC.SERVER_ID || ':%'
        AND USERNAME = SDEREC.OWNER;
        BEGIN
            FOR KILLREC IN KILL_DC LOOP
                SQL_STMT := 'ALTER SYSTEM KILL SESSION ' || '''' || KILLREC.SID || ',' || KILLREC.SERIAL# || '''';
                EXECUTE IMMEDIATE SQL_STMT;
            END LOOP;
        END;
    END LOOP;
    SDE.PINFO_UTIL.PURGE_UNUSED(NEWC);
END;
/

[AxelGriner]

Brent,

What is the status of the new tools that you guys are creating for managing connections through ArcGIS? Is that going to be part of 10.1? Thanks for any info.

Axel

[SørenAndersen]

I think there is a problem in the kill script, because the server_id is not unique. How can you find the right entry in v$session when you have more than one entry with the same server_id?

1183874 2760 24-06-2011 05:57:00 AGS Y Win32 s504052
1183904 2760 24-06-2011 07:12:34 N1MDVK Y Win32 PC5004408