Technical Article

USER PROCESS CHECK (COMPLETE)

,

The following runable script is a very simple way to track SPID's and PROCESS information (VERY BASIC) in a small target (Local) database.

1. Creates a small database
1.1 must set MDF and LDF (File location information) specific to the drive and file folder locations for each server
2. Sets All db options
3. Creates single table to store data
4. Creates a two step job to load / delete data every day

-- 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:

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating