Wednesday, 31 December 2014

Quick SQL Reporting - processes and blocking

A quick Qlikview/SQL script to process monitoring - in place of using SP_WHO2




As Sp_who2 has the column spid twice, it cannot be loaded into Qlikview - an alternative script is as below.

LOAD  spid ,
        status ,
        loginame ,
        hostname ,
        BlockingProcess,
        DATABASE ,
        cpu ,
        cmd ,
        nt_domain ,
        nt_username ,
        waittime ,
        login_time ,
        program_name ,
        request_id;
SQL SELECT  spid ,
        status ,
        loginame ,
        hostname ,
        BlockingProcess = blocked ,
        [DATABASE] = DB.name ,
        cpu ,
        cmd ,
        nt_domain ,
        nt_username ,
        waittime ,
        login_time ,
        program_name ,
        request_id
FROM    master..sysprocesses SP
        LEFT JOIN master.sys.databases DB ON SP.dbid = DB.database_id;