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)

You rated this post out of 5. Change rating

2003-02-06

202 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)

You rated this post out of 5. Change rating

2003-02-05

1,221 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)

You rated this post out of 5. Change rating

2002-08-19

2,092 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 […]

You rated this post out of 5. Change rating

2002-06-14

570 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)

You rated this post out of 5. Change rating

2002-06-06

3,678 reads

Technical Article

Alert Procedure for Long-Running Job

  • Script

For jobs that run periodically and should take only a short time to run, a DBA may want to know when the job has been running for an excessive time. In this case, just checking to see IF the job is running won't do; the ability to make sure that it hasn't been running for […]

4.8 (5)

You rated this post out of 5. Change rating

2002-01-15

3,961 reads

Blogs

SQL Server Quickie #46 – SQL Server on Linux

By

Today I have uploaded SQL Server Quickie #46 to YouTube. This time I’m talking...

Giving a Security Webinar this Wednesday

By

If you haven’t already signed up, on November 29, 2023, at 11 AM Eastern...

Write to backup block blob device failed. Device has reached its limit of allowed blocks.

By

Picture this, you’re happily backing up your database to a Azure blob storage until...

Read the latest Blogs

Forums

An Incomplete Window Definition

By Steve Jones - SSC Editor

Comments posted to this topic are about the item An Incomplete Window Definition

A Lack of Privacy in Smart Cars

By Steve Jones - SSC Editor

Comments posted to this topic are about the item A Lack of Privacy in...

Using the Import Extension in Azure Data Studio

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Using the Import Extension in...

Visit the forum

Ask SSC Logo Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers