Report for Server Activity

USE [rmsprod]
GO
/****** Object:  StoredProcedure [rms].[PROC_SERVER_ACTIVITY]    Script Date: 10/19/2010 15:39:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [rms].[PROC_SERVER_ACTIVITY]

AS

BEGIN

/*****************************************************************************************
Stored Procedure Name:   [dbo].[SERVER_ACTIVITY]
Date Written:            AUGUST 2010
Author: CHITANYA CHITTURI                                  
Project/System:                                                      
Purpose/Description:    
Input Parameters:    
Output Parameters:                                                
Tables:                        
Procedures Called By:                        
Procedures Called:      
Return Status:                                                    
Error Codes:                                                              
Reports:    
                                                                          
CHANGE HISTORY:                                                          
Date Modified   Modified By   Description of Modification                
-------------   -----------   ------------------------------------------------------
*****************************************************************************************/

IF OBJECT_ID('tempdb..#SP_WHO2_ACTIVE') IS NOT NULL
DROP TABLE #SP_WHO2_ACTIVE

/* CREATE A TEMPORARY TABLE*/

CREATE TABLE #SP_WHO2_ACTIVE
(SPID INT,
STATUS VARCHAR(1000) NULL,
LOGIN VARCHAR(1000) NULL,
HOSTNAME VARCHAR(1000) NULL,
BLKBY VARCHAR(1000) NULL,
DBNAME VARCHAR(1000) NULL,
COMMAND VARCHAR(1000) NULL,
CPUTIME INT NULL,
DISKIO INT NULL,
LASTBATCH VARCHAR(1000) NULL,
PROGRAMNAME VARCHAR(1000) NULL,
SPID2 INT)

INSERT
INTO #SP_WHO2_ACTIVE
EXEC sp_who2 active


/*CREATING THE TABLE FOR THE ENTIRE SP_WHO2 VALUES*/

CREATE TABLE #SP_WHO2_ALL
(SPID INT,
STATUS VARCHAR(1000) NULL,
LOGIN VARCHAR(1000) NULL,
HOSTNAME VARCHAR(1000) NULL,
BLKBY VARCHAR(1000) NULL,
DBNAME VARCHAR(1000) NULL,
COMMAND VARCHAR(1000) NULL,
CPUTIME INT NULL,
DISKIO INT NULL,
LASTBATCH VARCHAR(1000) NULL,
PROGRAMNAME VARCHAR(1000) NULL,
SPID2 INT)


INSERT
INTO #SP_WHO2_ALL
EXEC sp_who2

--select * from #SP_WHO2_
/*CREATING THE 2ND TEMPORARY TABLE FOR GETTING THE REQUIRED LIST OF SPIDS*/


IF OBJECT_ID('tempdb..#SP_WHO2_UNIQUE') IS NOT NULL
DROP TABLE #SP_WHO2_UNIQUE

CREATE TABLE #SP_WHO2_UNIQUE
(SPID INT,
LOGIN VARCHAR(1000) NULL,
HOSTNAME VARCHAR(1000) NULL,
BLKBY VARCHAR(1000) NULL,
RESOURCE_USAGE INT NULL,
PROGRAMNAME VARCHAR(1000) NULL)


INSERT INTO #SP_WHO2_UNIQUE(SPID,LOGIN,HOSTNAME,BLKBY,RESOURCE_USAGE,PROGRAMNAME)
SELECT
SP.SPID,
SP.LOGIN,
SP.HOSTNAME,
BLKBY = MAX(CASE WHEN SP.BLKBY = '  .  ' THEN 0  else SP.BLKBY END),
SUM(SP.CPUTIME + SP.DISKIO),
SP.PROGRAMNAME
FROM
#SP_WHO2_ACTIVE SP
WHERE
SP.DBNAME IS NOT NULL
AND SP.SPID <> @@SPID
AND SP.SPID > 50
GROUP BY
SP.SPID,
SP.LOGIN,
SP.HOSTNAME,
--SP.BLKBY,
SP.PROGRAMNAME
HAVING
SUM(SP.CPUTIME + SP.DISKIO) > 0

UPDATE
#SP_WHO2_UNIQUE

SET LOGIN   =  CASE
WHEN UPPER(DW2.QUEUE_ID) IS NULL  AND (SP2.PROGRAMNAME) LIKE '%0X78F68D8C8FC4BA48A12C2B095CF7BC60%' THEN 'SQL Service'
WHEN UPPER(SP2.LOGIN) = 'SA' THEN 'System Administrator'
WHEN UPPER(SP2.LOGIN) LIKE 'BHSFDOMAIN\%' THEN
CASE
WHEN UPPER(SP2.PROGRAMNAME) LIKE '%0X78F68D8C8FC4BA48A12C2B095CF7BC60%' THEN U1.Firstname + ' ' + U1.LastName
ELSE U.Firstname + ' ' + U.LastName
END
  ELSE SP2.LOGIN COLLATE DATABASE_DEFAULT
END,
BLKBY     =   CASE
 WHEN SP2.BLKBY = 0 THEN 'N/A'
 WHEN SP2.SPID = SP2.BLKBY THEN 'N/A'
ELSE
--  WHEN UPPER(SP3.LOGIN) LIKE 'BHSFDOMAIN\%' THEN
CASE
WHEN UPPER(SP3.PROGRAMNAME) LIKE '%0X78F68D8C8FC4BA48A12C2B095CF7BC60%' THEN U3.Firstname + ' ' + U3.LastName + ' (' + CONVERT(VARCHAR(100),SP3.SPID) + ')'
ELSE U2.Firstname + ' ' + U2.LastName + ' (' + CONVERT(VARCHAR(100),SP3.SPID) + ')'
END
--   ELSE SP3.LOGIN COLLATE DATABASE_DEFAULT
END,

PROGRAMNAME = CASE  WHEN UPPER(SP2.PROGRAMNAME) LIKE '%LITESPEED%' THEN 'Backups in Progress'
WHEN UPPER(SP2.PROGRAMNAME) = 'USOFT DEVELOPER' THEN 'Prims'
WHEN UPPER(SP2.PROGRAMNAME) LIKE 'MICROSOFT%' OR UPPER(SP2.PROGRAMNAME) LIKE '%QUERY ANALYZER%' THEN 'SQL Query (DBA)'
WHEN UPPER(SP2.PROGRAMNAME) = '.NET SQLCLIENT DATA PROVIDER'  THEN CASE
WHEN UPPER(SP2.HOSTNAME)= 'BHSF-BTR-W322' THEN 'Pharmacy Web'
WHEN UPPER(SP2.HOSTNAME)= 'BHSF-BTR-W336' THEN 'SQL Reports'
ELSE 'DBA Development' END
WHEN UPPER(SP2.PROGRAMNAME) LIKE 'SQLAGENT - TSQL JOBSTEP%' THEN CASE
WHEN UPPER(SP2.PROGRAMNAME) LIKE '%0X78F68D8C8FC4BA48A12C2B095CF7BC60%' THEN 'Claims Report'
ELSE 'SQL Job In Progress'
END
WHEN UPPER(SP2.PROGRAMNAME) = 'DTS DESIGNER' THEN 'Data Transformation (DBA)'
ELSE UPPER(SP2.PROGRAMNAME)
END
FROM
#SP_WHO2_UNIQUE SP2
LEFT JOIN
#SP_WHO2_ALL SP3
ON SP3.SPID = SP2.BLKBY
LEFT JOIN
unodwdev.dbo.[User] U
ON UPPER(U.UserName) = UPPER(SUBSTRING(SP2.LOGIN,12,50)) COLLATE DATABASE_DEFAULT
LEFT JOIN
[BHSF-BTR-W22\SQL2005].DW_STAGING.dbo.QUERY_QUEUE_SQL_2000 DW1
ON DW1.SPID = SP2.SPID
LEFT JOIN
unodwdev.dbo.[User] U1
ON U1.UserName = DW1.REQUESTED_BY
LEFT JOIN
unodwdev.dbo.[User] U2
ON UPPER(U2.UserName) = UPPER(SUBSTRING(SP3.LOGIN,12,50)) COLLATE DATABASE_DEFAULT
LEFT JOIN
[BHSF-BTR-W22\SQL2005].DW_STAGING.dbo.QUERY_QUEUE_SQL_2000 DW2
ON DW2.SPID = SP3.SPID
LEFT JOIN
unodwdev.dbo.[User] U3
ON U3.UserName = DW2.REQUESTED_BY
WHERE
DW2.PROCESS_END_DATE IS NULL


SELECT
SPID,
LOGIN ,
BLKBY,
RESOURCE_USAGE,
PROGRAMNAME
FROM
#SP_WHO2_UNIQUE

END