USER PROCESS CHECK (COMPLETE)

-- Simple User Process Check
-- 01-04-2004
-- Jbabington@hotmail.com

-- 1. Create Simple DB
-- 1.1 Update the Filename location information

USE MASTER
IF EXISTS (SELECT name FROM master.dbo.sysdatabases 
WHERE name = N'USER_PROCESS_CHECK')
DROP DATABASE [USER_PROCESS_CHECK]
GO

CREATE DATABASE [USER_PROCESS_CHECK]  
ON (NAME = N'USER_PROCESS_CHECK_Data'

--, FILENAME = N'e:\MSSQL\data\USER_PROCESS_CHECK_DATA.MDF' 

, FILENAME = N'C:\USER_PROCESS_CHECK_DATA.MDF' 
, SIZE = 10, FILEGROWTH = 10%) 

LOG   ON (NAME = N'USER_PROCESS_CHECK_LOG'

--, FILENAME = N'e:\MSSQL\data\USER_PROCESS_CHECK_LOG.LDF'

, FILENAME = N'C:\USER_PROCESS_CHECK_LOG.LDF' 
, SIZE = 1, FILEGROWTH = 10%)

GO

exec sp_dboption N'USER_PROCESS_CHECK', N'autoclose', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'bulkcopy', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'trunc. log', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'torn page detection', N'true'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'read only', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'dbo use', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'single', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'autoshrink', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'ANSI null default', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'recursive triggers', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'ANSI nulls', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'concat null yields null', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'cursor close on commit', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'default to local cursor', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'quoted identifier', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'ANSI warnings', N'false'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'auto create statistics', N'true'
GO

exec sp_dboption N'USER_PROCESS_CHECK', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'USER_PROCESS_CHECK', N'db chaining', N'false'
GO

USE USER_PROCESS_CHECK
GO

if exists (select * 
from dbo.sysobjects 
where id = object_id(N'[dbo].[USER_PROCESSES]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[USER_PROCESSES]
GO

CREATE TABLE [dbo].[USER_PROCESSES] (
[BP_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
[Current Server] [varchar] (128) NOT NULL,
[SPID] [smallint] NOT NULL ,
[DBID] [smallint] NOT NULL ,
[DBName] [varchar] (128) NOT NULL ,
[Blocked] [smallint] NOT NULL ,
[Waittime] [int] NOT NULL ,
[Last wait type] [nchar] (32) NOT NULL ,
[Last batch] [datetime] NOT NULL ,
[Status] [varchar] (30) NOT NULL ,
[Hostname] [varchar] (256) NOT NULL ,
[Program Name] [varchar] (256) NOT NULL ,
[Last command] [varchar] (256) NOT NULL ,
[Event Time] [datetime] NOT NULL 
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[USER_PROCESSES] WITH NOCHECK ADD 
CONSTRAINT [PK_USER_PROCESSES] PRIMARY KEY  CLUSTERED 
(
[BP_ID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[USER_PROCESSES] ADD 
CONSTRAINT [DF_USER_PROCESSES_Event_Date] DEFAULT (getdate()) FOR [Event Time]
GO



-- Create Simple Job to run every minute and load the Process data


BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) 
FROM msdb.dbo.syscategories 
WHERE name = N'Database Maintenance') < 1 
EXECUTE msdb.dbo.sp_add_category 
@name = N'Database Maintenance'

-- Delete the job with the same name (if it exists)
  
SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'USER_PROCESS_CHECK')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
-- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
BEGIN 
    -- There is, so abort the script 
RAISERROR (N'Unable to import job ''USER_PROCESS_CHECK'' since there is already a multi-server job with this name.', 16, 1) 
GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'USER_PROCESS_CHECK' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT 
, @job_name = N'USER_PROCESS_CHECK'
, @owner_login_name = N'sa'
, @description = N'Simple Load Job to run every minute to track user processes in User databases'
, @category_name = N'Database Maintenance'
, @enabled = 1
, @notify_level_email = 0
, @notify_level_page = 0
, @notify_level_netsend = 0
, @notify_level_eventlog = 2
, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

-- Add the job steps
  
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID
, @step_id = 1
, @step_name = N'DELETE DATA OVER 7 DAYS'
, @command = N'DELETE FROM USER_PROCESSES WHERE [EVENT TIME] > GETDATE()  -7'
, @database_name = N'USER_PROCESS_CHECK'
, @server = N''
, @database_user_name = N''
, @subsystem = N'TSQL'
, @cmdexec_success_code = 0
, @flags = 0
, @retry_attempts = 0
, @retry_interval = 1
, @output_file_name = N''
, @on_success_step_id = 0
, @on_success_action = 3
, @on_fail_step_id = 0
, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID
, @step_id = 2
, @step_name = N'INSERT PROCESS DATA'
, @command = N'INSERT INTO USER_PROCESSES
SELECT @@ServerName AS [Current Server]
, a.SPID
, b.DBID As [DBID]
, b.Name AS [DBName]
, a.Blocked
, a.Waittime
, a.LastwaittypeAS [Last wait type]
, a.Last_batchAS [Last batch]
, a.Status
, a.hostnameAS [Hostname]
, a.program_nameAS [Program Name]
, Cmd AS [Last Command]
,GetDate() AS [Event Time]
FROM Master.dbo.sysprocesses a
, Master.dbo.Sysdatabases b 
WHERE  a.DBID NOT IN (0,1,2,3,4,5,6)
AND b.Name != (''distribution'')'
, @database_name = N'USER_PROCESS_CHECK'
, @server = N''
, @database_user_name = N''
, @subsystem = N'TSQL'
, @cmdexec_success_code = 0
, @flags = 0
, @retry_attempts = 0
, @retry_interval = 1
, @output_file_name = N''
, @on_success_step_id = 0
, @on_success_action = 1
, @on_fail_step_id = 0
, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID
, @start_step_id = 1 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 




-- Add the job schedules
  
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID
, @name = N'CHECK USER PROCESSES - MINUTE'
, @enabled = 1
, @freq_type = 4
, @active_start_date = 20050104
, @active_start_time = 0
, @freq_interval = 1
, @freq_subday_type = 4
, @freq_subday_interval = 1
, @freq_relative_interval = 0
, @freq_recurrence_factor = 0
, @active_end_date = 99991231
, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

-- Add the Target Servers
  
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID
, @server_name = N'(local)' 
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: