Log SQL Connections and Locks

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.

Calculate the table size and number of rows

At some point you might need to know, how many tables you have in database, number of rows in each table, size of table and indices. Following is a simple SQL Script which can provide you a snapshot of all such details.

---

 Declare @TabName Varchar(100)

Create table #TempTable (Name varchar(100), Rows int,Reserved varchar(25),Data varchar(25),Index_size varchar(25),Unused varchar(25))

 

Declare TableList cursor LOCAL FAST_FORWARD FOR     

SELECT Name FROM sysobjects

WHERE xtype='U'

ORDER by Name

OPEN TableList     

     

FETCH NEXT FROM TableList INTO @TabName         

WHILE @@fetch_status = 0        

 BEGIN   

        INSERT INTO #TempTable Exec sp_spaceused @TabName

        FETCH NEXT FROM TableList INTO @TabName   

 End

CLOSE TableList
DEALLOCATE TableList

SELECT * FROM #TempTable

---