Lock and Connection Management

Technical Article

Correction to "drop/recreate objects" script.

  • Script

Regarding the recent script I submitted (dropping/recreating all procedures/views) - I made an important oversight. I neglected to add a CASE statement in order to make sure that the appropriate type of object was being referenced in the DROP statement.Below is the corrected script:

5 (1)


200 reads

Technical Article

Drop and re-create all stored procedures or views

  • Script

There are times when you may need to drop and re-create all stored procedures and/or views in your database.  For example, in cases where procedures or views are causing blocked locks or other performance problems, a recent article (http://www.sswug.org/see.asp?s=1166&id=13448) suggested dropping/re-creating procedures and views after a service pack has been installed.  The installation of a […]

1.67 (3)


1,164 reads

Technical Article

SP to display locking users in tree format

  • Script

This SP will give a listing of users blocking and being blocked in a tree formation (similar to explorer tree list). It uses a User defined function that recurses through the locking information gathered from sysprocesses. The listing only shows the Hostname (username) and the SPID.First create the User Defined Function (Part One) then create […]

2 (1)


2,038 reads

Technical Article

System Lock Snapshot

  • Script

This script captures the current system lock in a temp table, then builds a temp translation table for database and object id by iterating through all of the databases.  It then produces a report by joining the two tables.For better performance, you could make the translation table perminante and only update it when you are […]


568 reads

Technical Article

sp_lock2  = sp_lock +shows database and object name

  • Script

sp_lock2 is similar to sp_lock, except that it displays the database name, object name and index name instead of the ids.  It accepts no parameters unlike the sp_lock procedure which can take an optional spid parameter. The basis for the main query which queries the system tables for lock info was taken from the sp_lock […]

4.5 (2)


3,098 reads

Technical Article

Show Blocking and Wait time

  • Script

When executed against a database in which blocking occur, below script will report lockType, Object waited for and current Wait times. The script requires access to master..systables. The script queries syslockInfo (as does sp_lock), but further joins sysprocesses with an interpretation of waitresource matching SQL Server 7.0 and SQL Server 2000.


2,525 reads


Daily Coping 4 Jul 2022


I started to add a daily coping tip to the SQLServerCentral newsletter and to...

SQLServerCarpenter Tools


Over the past couple of years, I’ve developed several tools that I’ve been using...



/* Author:Brahmanand Shukla (SQLServerCarpenter.com) Date:27-May-2022 Purpose:To get all the stored procedures and triggers missing the use of...

Read the latest Blogs


CAST AS DATE Assistance

By carlton 84646

Can someone let me know why I'm not able to use CAST method to...

Deploying a standalone Azure VM running SQL Server into an availability zone

By DBANewbie

If i deploy a standalone Azure VM running SQL Server into an availavility zone;...

SQL Server 2014 Question

By kazana

So I have inherited an older SQL 2014 Server and have been working on...

Visit the forum

Ask SSC Logo Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers