locked
Dependency Evaluation , Finding active connections RRS feed

  • Question

  • Hi, I am trying to find the list of active connections on SQL SERVER , more and more granular way ...
    like below , I know a way using active monitor or Profiler  or some DMVs give me some list , but is there a way to find 

    what is the server name ,instance name connectedc to it  , who is the clinet connected to it , whats the application being used now?
    what database  it is connected to it ? what login being used to connect ? what table and column being used right now and what kind of DML or DDL are happeming ?
    I want to create a report and find Dependancay evaluation so that we will re architech or re design the existing databases ...



    ServerName  InstanceName  Client  Application  Database  Login  Table  Column  Select  Insert  Update  Delete  Truncate  Alter

    Please give me some some suggestions to start with at-least 

    thanks


    Tuesday, May 19, 2015 8:24 PM

Answers

  • try adam Mechanic's Sp_whoisactive -- it can give most of the information you want and it will give you the actually Tsql that is executing... 

    google - sp_whosiactive to download ...

    For more insights, you can use - sql trace or extended events and log the information. if you want to monitor foe certain period, you can use this method..

    for dependency - you can use the - view dependencies property in ssms on each table to get the dependencies list. it will not count every scenerio, you might have but should help to get the idea..

    you can also use - builtin - sp_who2 to get some of this information..


    Hope it Helps!!


    Tuesday, May 19, 2015 8:32 PM
  • any one else can help with any other way?

    one other solution could be database auditing... this is only enterprise feature.. you might not get all the information.. but most of them but you need to set up what events you want to audit...


    Hope it Helps!!

    Tuesday, May 19, 2015 10:16 PM

All replies

  • try adam Mechanic's Sp_whoisactive -- it can give most of the information you want and it will give you the actually Tsql that is executing... 

    google - sp_whosiactive to download ...

    For more insights, you can use - sql trace or extended events and log the information. if you want to monitor foe certain period, you can use this method..

    for dependency - you can use the - view dependencies property in ssms on each table to get the dependencies list. it will not count every scenerio, you might have but should help to get the idea..

    you can also use - builtin - sp_who2 to get some of this information..


    Hope it Helps!!


    Tuesday, May 19, 2015 8:32 PM
  • Thanks .. will try that
    Tuesday, May 19, 2015 8:40 PM
  • any one else can help with any other way?
    Tuesday, May 19, 2015 10:08 PM
  • any one else can help with any other way?

    one other solution could be database auditing... this is only enterprise feature.. you might not get all the information.. but most of them but you need to set up what events you want to audit...


    Hope it Helps!!

    Tuesday, May 19, 2015 10:16 PM
  • I have used Profiler, Database audit, and also SP_who2 like below :

    CREATE TABLE #sp_who2 

    SPID INT, 
    Status VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT ,
    RequestId INT



    Create Table #SqlStatement  (spid int, statement varchar(8000))

    create table #temp (x varchar(100), y int, s varchar(1000), id INT IDENTITY (1,1))


    INSERT #sp_who2 EXEC sp_who2 



    Declare @spid varchar(10)
    Declare @Statement varchar(8000)
    declare @sql varchar(1000)
    DECLARE SpidCursor Cursor 
    FOR Select spid from #sp_who2
    OPEN SpidCursor
    FETCH NEXT FROM SpidCursor
    INTO @spid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'dbcc inputbuffer (' + @spid + ')'
    insert #temp
    exec (@sql)
    Insert Into #SqlStatement (spid,Statement)
    Select @spid, s From #Temp where id = (Select max(id) from #Temp)
    FETCH NEXT FROM SpidCursor
    INTO @spid
    END
    Close SpidCursor
    Deallocate SpidCursor

    Select B.Statement, A.* from #sp_who2 A 
    LEFT JOIN
    #SqlStatement B ON A.spid = B.spid

    so , i got SQL text / text data(which include all statements)  from Auditing,Profiler, also from SP_who2  ;

    nhow How can i get  fromm sql text ...

    Table_name  column_name    Select   Insert Update Delete Alter Drop 

    account        id                           1       1        1         1        1      0

    Account      Name                        1      1       0         0        0       0

    Customer     Address                  1        0      1        0       0         0

    OR 

    Table_name      Select                      Insert      Update         Delete            Alter                  Drop 

    account              ID                           Name       ID           SSN                   State                   NULL 

    Account              Name                      NULL        NULL  

    Customer                 NAme                                    NULL 

     

    some thing like this 

    can anyone plz help

    Friday, May 29, 2015 2:35 PM