3 Comments

  1. Cláudio Silva

    Thanks for share Derek Wilson.

    I suggest you to use the ‘sys.sql_modules’ instead of ‘Information_Schema.Routines’.

    The main reason it is because the column ROUTINE_DEFINITON is only NVARCHAR(4000) whereas the column DEFINITION on ‘sys.sql_modules’ is NVARCHAR(MAX).

    This means that you maybe cannot find what you are looking for because the text will be truncated.

    For those who uses ‘syscomments’ the problem is the same plus is deprecated (http://msdn.microsoft.com/en-us/library/ms186293.aspx)

    Best regards,
    Cláudio Silva

  2. — Here is a version of the sp_who2 query you can use to filter the results you get

    select
    SPID,
    Status,
    convert(sysname, rtrim(loginame)) as loginname,
    HostName ‘HostName’,
    Blocked ‘BlkBy’,
    db_name(dbid) ‘DBName’,
    cmd ‘Command’,
    cpu ‘CPUTime’,
    physical_io ‘DiskIO’,
    substring( convert(varchar,last_batch,111) ,6 ,5 ) + ‘ ‘
    + substring( convert(varchar,last_batch,113) ,13 ,8 )
    as ‘last_batch_char’,
    program_name,–ProgramName,
    spid ‘SPID’,
    hostprocess,
    net_address,
    ” ‘ 0) or
    –(spid in (select blocked from master..sysprocesses with (nolock) where blocked > 0)))

    –sample filters-
    –and spid = 55
    –and hostname like ‘I%’

    –Column name Data type Description
    –=========== ========= ===========
    –spid smallint SQL Server process ID.
    –kpid smallint Microsoft Windows NT 4.0® thread ID.
    –blocked smallint Process ID (spid) of a blocking process.
    –waittype binary(2) Reserved.
    –waittime int Current wait time in milliseconds. Is 0 when the process is not waiting.
    –lastwaittype nchar(32) A string indicating the name of the last or current wait type.
    –waitresource nchar(32) Textual representation of a lock resource.
    –dbid smallint ID of the database currently being used by the process.
    –uid smallint ID of the user who executed the command.
    –cpu int Cumulative CPU time for the process. The entry is updated for all processes, regardless of whether the SET STATISTICS TIME ON option is ON or OFF.
    –physical_io int Cumulative disk reads and writes for the process.
    –memusage int Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
    –login_time datetime Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is stored.
    –last_batch datetime Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which SQL Server startup occurred is stored.
    –ecid smallint Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
    –open_tran smallint Number of open transactions for the process.
    –status nchar(30) Process ID status (for example, running, sleeping, and so on).
    –sid binary(85) Globally unique identifier (GUID) for the user.
    –hostname nchar(128) Name of the workstation.
    –program_name nchar(128) Name of the application program.
    –hostprocess nchar(8) Workstation process ID number.
    –cmd nchar(16) Command currently being executed.
    –nt_domain nchar(128) Windows NT 4.0 domain for the client (if using Windows Authentication) or a trusted connection.
    –nt_username nchar(128) Windows NT 4.0 user name for the process (if using Windows Authentication) or a trusted connection.
    –net_address nchar(12) Assigned unique identifier for the network interface card on each user’s workstation. When the user logs in, this identifier is inserted in the net_address column.
    –net_library nchar(12) Column in which the client’s network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. For more information, see Client and Server Net-Libraries.
    –loginame nchar(128) Login name.
    –sql_handle binary(20) Represents the currently executing batch or object.
    –stmt_start int Starting offset of the current SQL statement for the specified sql_handle.
    –stmt_end int Ending offset of the current SQL statement for the specified sql_handle.
    —1 indicates that the current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.

    –Last Modified Date: 09-30-2010ID: 57785

Leave a Reply

Your email address will not be published. Required fields are marked *