This script creates a stored procedure that shows you a list of all the users that are locking objects on the database with mode X.
It shows a full and detailed description on the user and object locked.
Rodrigo Acosta,
2001-09-06
This script creates a stored procedure that shows you a list of all the users that are locking objects on the database with mode X.
It shows a full and detailed description on the user and object locked.
CREATE PROCEDURE proc_lock AS SET NOCOUNT ON CREATE TABLE #lock (spid int, dbid int, objid int, indid int, type varchar(10), resource varchar(40), mode varchar(10), status varchar(30)) CREATE TABLE #who (spid int, status varchar(30), loginame varchar(20), hostname varchar(15), blk int, dbname varchar(20), cmd varchar(60)) INSERT INTO #who EXEC sp_who INSERT INTO #lock EXEC sp_lock SELECT 'X Type Lockins'='The user '+w.loginame+'(id:'+CONVERT(VARCHAR(5),w.spid)+') in Server '+w.hostname+' using the Database '+d.name+'(id:'+CONVERT(VARCHAR(5),l.dbid)+') is locking a '+l.type+' with mode '+l.mode FROM #who w INNER JOIN #lock l ON w.spid=l.spid INNER JOIN master..sysdatabases d ON d.dbid=l.dbid where l.mode like '%X%' DROP TABLE #who DROP TABLE #lock
You rated this post out of 5. Change rating
This script is intended for SQL Server professionals that needs to have a quick snapshot from an environment with all relevant information about the databases. It's a compilation from multiple scripts, and works in SQL Server 2000.I think that works on 7.0 too, but may have some compilation issues.Don´t forget to see the section MANUAL […]
2003-04-28
4,683 reads
It calculates log free space in mb and in percent for all databases, and also show the quantity of different objects for one databases.
2002-02-02
2,723 reads
Like the one posted before, the use of the SP is like this: proc_alerts 1,18. This will show the first 18 alerts on the system. OR proc_alerts 20,35 will show from 20 to 35. This lets you manage the list without letting go out of the screen.
2001-11-03
1,034 reads
If you are using OSQL or ISQL from DOS querys are really hard to use, because the result is to big for the screen. With this procedure, you can especify from wich column to wich column show the list of tables. For Example: proc_tables master, 3, 10 will show you only a short list of […]
2001-10-28
728 reads
This SP checks the path where SQL Server is installed, and then lists all the .mdf and .ldf files. Also list the quantity of tables, views, sp, xp and users on the given database.
2001-09-10
2,746 reads