Vishwamohan

You might have come across situation when you wanted to log SQL Connections and Locks for a particular day or period. In the following example, by using two tables and one stored procedure, you can log these activities and analyze at a later time. This stored procedure can be turned into a SQL Job for any given interval so that you do not have to manually run it.

Step 1 : Create following 2 tables.

 If Exists(SELECT * FROM Sysobjects WHERE name='tbl_Who2_Log'AND Type='U')

 Drop Table tbl_Who2_Log

Go

 

CREATE TABLE [dbo].[tbl_Who2_Log](

            [RID] [bigint] IDENTITY(1,1) NOT NULL,

            [SPID] [bigint] NOT NULL,

            [Status] [varchar](100) NULL,

            [Login] [varchar](100) NULL,

            [HostName] [varchar](200) NULL,

            [BlkBy] [varchar](50) NULL,

            [DBName] [varchar](50) NULL,

            [Command] [varchar](200) NULL,

            [CPUTime] [bigint] NULL,

            [DiskIO] [bigint] NULL,

            [LastBatch] [varchar](20) NULL,

            [ProgramName] [varchar](200) NULL,

            [SSPID] [bigint] NULL,

            [LogDate] [datetime] NOT NULL

           CONSTRAINT [DF_tbl_Who2_Log_LogDate] DEFAULT (getdate())         

) ON [PRIMARY]

 

GO

 

ALTER TABLE dbo.tbl_Who2_Log

       ADD CONSTRAINT PK_tbl_Who2_Log PRIMARY KEY ([LogDate] ASC,[RID] ASC)

Go

 

 

If Exists(SELECT * FROM Sysobjects WHERE name='tbl_Lock_Log'AND Type='U')

 Drop Table tbl_Lock_Log

Go

 

CREATE TABLE [dbo].[tbl_Lock_Log](

            [RID] [bigint] IDENTITY(1,1) NOT NULL,

            [SPID] [bigint] NOT NULL,

            [DBID] [bigint] NULL,

            [ObjId] [bigint] NULL,

            [IndId] [bigint] NULL,

            [Type] [varchar](25) NULL,

            [Resource] [varchar](25) NULL,

            [Mode] [varchar](25) NULL,

            [Status] [varchar](25) NULL,

            [LogDate] [datetime] NOT NULL

           CONSTRAINT [DF_tbl_Lock_Log_LogDate] DEFAULT (getdate())

) ON [PRIMARY]

 

GO

 

ALTER TABLE dbo.[tbl_Lock_Log]

       ADD CONSTRAINT PK_tbl_Lock_Log PRIMARY KEY ([LogDate] ASC,[RID] ASC)

Go

 

Step 2:  Create following Stored Procedure

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].Usp_Log_WhoLock')

                                                     and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure Usp_Log_WhoLock

GO

 

CREATE Procedure dbo.Usp_Log_WhoLock

As

 

/*****************************

* Procedure:        Usp_Log_WhoLock

* PURPOSE:          Logs SQL Connection and Locks

* NOTES:    

* CREATED:      Vishwa(06/24/2006)

*

* MODIFIED  

* DATE                AUTHOR                 DESCRIPTION

*-------------------------------------------------------------------------------

* {date}                {developer}        {brief modification description}

*

*                                              

************************************/ 

SET NOCOUNT ON

Declare @ConnectionCount Int, @LockCount Int

Declare @CurrentTime DateTime

SET @CurrentTime= GetDate()

 

-----FOR SQL Server 2000 based -----------------------

INSERT Into tbl_Who2_Log(spid, Status, Login, HostName, BlkBy, DBName,

                        Command, CPUTime,DiskIO,LastBatch,ProgramName,SSPID)

EXEC dbo.sp_who2

 

/***FOR SQL Server 2005 based ------------------------

 INSERT Into tbl_Who2_Log(spid, Status, Login, HostName, BlkBy, DBName,
                        Command, CPUTime,DiskIO,LastBatch,ProgramName,SSPID)
SELECT spid, Status, Loginame, HostName, Blocked, dbid,
        cmd, CPU,physical_IO,Last_Batch,Program_Name,hostprocess
FROM master.dbo.sysprocesses

*******************/

 

INSERT tbl_Lock_Log (spid,dbid,ObjId,IndId,Type, Resource,Mode,Status)

EXEC dbo.sp_lock

 

 

SELECT @ConnectionCount = COUNT(SPID)

FROM tbl_Who2_Log (NOLOCK)

WHERE DateDiff(Minute,LogDate,@CurrentTime)=0

 

 

SELECT @LockCount = COUNT(SPID)

FROM tbl_Lock_Log (NOLOCK)

WHERE DateDiff(Minute,LogDate,@CurrentTime)=0

 

 

PRINT 'At ' + Convert(VarChar(20),@CurrentTime)

                        + ' Total Connections Count: ' +

                        Convert(VarChar,@ConnectionCount) +

                        ' and Total Locks Count: ' + Convert(VarChar,@LockCount)

 

 

SET NOCOUNT OFF

Step 3: Turn the above Stored Procedure in to a SQL Job.

NOTE: You will need to clean up the logged transactions in above table on frequent basis otherwise these tables will start occupying too much space.