To view current locks

sp_lock

Reports information about locks.

Syntax

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

Arguments

[@spid1 =] 'spid1'

Is the Microsoft® SQL Server™ process ID number from master.dbo.sysprocesses. spid1 is int, with a default of NULL. Execute sp_who to obtain process information about the lock. If spid1 is not specified, information about all locks is displayed.

[@spid2 =] 'spid2'

Is another SQL Server process ID number to check for lock information. spid2 is int, with a default of NULL. spid2 is another spid that may have a lock at the same time as spid1, and on which the user also wants information.

Note  sp_who can have zero, one, or two parameters. These parameters determine whether the stored procedure displays locking information on all, one, or two spid processes.

Return Code Values

0 (success)

Result Sets

Remarks

Users can control locking by adding an optimizer hint to the FROM clause of a SELECT statement, or by setting the SET TRANSACTION ISOLATION LEVEL option..

In general, read operations, acquire shared locks, and write operations acquire exclusive locks. Update locks are acquired during the initial portion of an update operation when the data is being read. Update locks are compatible with shared locks. Later, if the data is changed, the update locks are promoted to exclusive locks. There are times when changing data that an update lock is briefly acquired prior to an exclusive lock. This update lock will then be automatically promoted to an exclusive lock.

Different levels of data can be locked including an entire table, one or more pages of the table, and one or more rows of a table. Intent locks at a higher level of granularity mean locks are either being acquired or intending to be acquired at a lower level of lock granularity. For example, a table intent lock indicates the intention to acquire a shared or exclusive page level lock. An intent lock prevents another transaction from acquiring a table lock for that table.

An extent lock is held on a group of eight database pages while they are being allocated or freed. Extent locks are set while a CREATE or DROP statement is running or while an INSERT or UPDATE statement that requires new data or index pages is running.

When reading sp_lock information, use the OBJECT_NAME( ) function to get the name of a table from its ID number, for example:

SELECT object_name(16003088)

All distributed transactions not associated with a SPID value are orphaned transactions. SQL Server 2000 assigns all orphaned distributed transactions the SPID value of '-2', making it easier for a user to identify blocking distributed transactions.

Permissions

Execute permissions default to the public role.

Examples

A. List all locks

This example displays information about all locks currently held in SQL Server.

USE master

EXEC sp_lock

B. List a lock from a single-server process

This example displays information, including locks, on process ID 53.

USE master

EXEC sp_lock 53

To view the current server activity

sp_who

Provides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.

Syntax

sp_who [[@login_name =] 'login']

Arguments

[@login_name =] 'login'

Is a user login name on SQL Server. login is sysname, with a default of NULL. If no name is specified, the procedure reports all active users of SQL Server. login can also be a specific process identification number (SPID). To return information on active processes, specify ACTIVE. ACTIVE excludes from the report processes that are waiting for the next command from the user.

Return Code Values

0 (success) or 1 (failure)

Result Sets

sp_who returns a result set with the following information.

The sp_who result set will be sorted in ascending order according to the spid values. In case of parallel processing, sub-threads are created for the specific spid. The main thread is indicated as spid =xxx and ecid =0. The other sub-threads have the same spid = xxx, but with ecid > 0. Thus, multiple rows for that spid number will be returned -- grouped together within that spid's placement in the overall list. The sub-threads will be listed in random order, except for the parent thread (ecid = 0), which will be listed first for that spid.

Remarks

A blocking process (which may have an exclusive lock) is one that is holding resources that another process needs.

In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. SQL Server 2000 reserves SPID values from 1 through 50 for internal use, while SPID values 51 or higher represent user sessions.

Permissions

Execute permissions default to the public role.

Examples

A. List all current processes

This example uses sp_who without parameters to report all current users.

USE master

EXEC sp_who

Here is the result set:

spid ecid status     loginame       hostname  blk dbname cmd                  

---- ---- ------     ------------   --------  --- ------ -----                

1    0    background sa                       0   pubs   LAZY WRITER          

2    0    sleeping   sa                       0   pubs   LOG WRITER           

3    0    background sa                       0   master SIGNAL HANDLER       

4    0    background sa                       0   pubs   RA MANAGER           

5    0    background sa                       0   master TASK MANAGER         

6    0    sleeping   sa                       0   pubs   CHECKPOINT SLEEP     

7    0    background sa                       0   master TASK MANAGER         

8    0    background sa                       0   master TASK MANAGER         

9    0    background sa                       0   master TASK MANAGER         

10   0    background sa                       0   master TASK MANAGER         

11   0    background sa                       0   master TASK MANAGER         

51   0    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      

51   2    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      

51   1    runnable   DOMAIN\loginX  serverX   0   Nwind  BACKUP DATABASE      

52   0    sleeping   DOMAIN\loginX  serverX   0   master AWAITING COMMAND     

53   0    runnable   DOMAIN\loginX  serverX   0   pubs   SELECT               

(16 row(s) affected)

B. List a specific user's process

This example shows how to view information about a single current user by login name.

USE master

EXEC sp_who 'janetl'

C. Display all active processes

USE master

EXEC sp_who 'active'

D. Display a specific process with process ID

USE master

EXEC sp_who '10' --specifies the process_id

To view the last command batch for a connection

DBCC INPUTBUFFER

Displays the last statement sent from a client to Microsoft® SQL Server™.

Syntax

DBCC INPUTBUFFER (spid)

Arguments

spid

Is the system process ID (SPID) for the user connection as displayed in the output of the sp_who system stored procedure.

Result Sets

DBCC INPUTBUFFER returns a rowset with the following columns.

For example, DBCC INPUTBUFFER returns the following result set when the last event in the buffer is DBCC INPUTBUFFER(11).

EventType      Parameters EventInfo             

-------------- ---------- --------------------- 

Language Event 0          DBCC INPUTBUFFER (11)

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note  There are very brief transitional periods between events when no event can be displayed on Windows NT®. On Windows 98, an event is displayed only when active.

Permissions

DBCC INPUTBUFFER permissions default to members of the sysadmin fixed server role only, who can see any SPID. Other users can see any SPID they own. Permissions are not transferable.

Examples

This example assumes a valid SPID of 10.

DBCC INPUTBUFFER (10)

To view the data space information for a database

sp_spaceused

Displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.

Syntax

sp_spaceused [[@objname =] 'objname']
    [,[@updateusage =] 'updateusage']

Arguments

[@objname =] 'objname'

Is the name of the table for which space usage information (reserved and allocated space) is requested. objname is nvarchar(776), with a default of NULL.

[@updateusage =] 'updateusage'

Indicates whether or not DBCC UPDATEUSAGE should be run within the database (when no objname is specified) or on a specific object (when objname is specified). Values can be true or false. updateusage is varchar(5), with a default of FALSE.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If objname is omitted, two result sets are returned.

If parameters are specified, this is the result set.

Remarks

sp_spaceused computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. If objname is not given, sp_spaceused reports on the space used by the entire current database.

When updateusage is specified, Microsoft® SQL Server™ scans the data pages in the database and makes any necessary corrections to the sysindexes table regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the sysindexes information for the table may not be current. This process can take some time to run on large tables or databases. Use it only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Permissions

Execute permissions default to the public role.

Examples

A. Space information about a table

This example reports the amount of space allocated (reserved) for the titles table, the amount used for data, the amount used for index(es), and the unused space reserved by database objects.

USE pubs

EXEC sp_spaceused 'titles'

B. Updated space information about a complete database

This example summarizes space used in the current database and uses the optional parameter @updateusage.

USE pubs

sp_spaceused @updateusage = 'TRUE'

Permissions

Execute permissions default to the public role.

To view the log space information for a database

sysperfinfo

Contains a Microsoft® SQL Server™ representation of the internal performance counters that can be displayed through the Windows NT Performance Monitor.

Note  The Windows NT Performance Monitor is available only when using Microsoft Windows NT® 4.0 as the operating system.

Performance condition alerts are only available for the first 99 databases. Any databases created after the first 99 databases will not be included in the sysperfinfo system table, and using the sp_add_alert procedure will return an error.

To view the oldest active transaction in the database

DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax

DBCC OPENTRAN
    (    { 'database_name' | database_id} )
        [ WITH TABLERESULTS
            [ , NO_INFOMSGS ]
        ]

Arguments

'database_name'

Is the name of the database for which to display the oldest transaction information. Database names must conform to the rules for identifiers.

database_id

Is the database identification (ID) number for which to display the oldest transaction information. Obtain the database ID using the DB_ID function.

WITH TABLERESULTS

Specifies results in a tabular format that can be loaded into a table. Use this option to create a table of results that can be inserted into a table for comparisons. When this option is not specified, results are formatted for readability.

NO_INFOMSGS

Suppresses all informational messages.

Remarks

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft® SQL Server™, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

Result Sets

DBCC OPENTRAN returns this result set when there are no open transactions:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC OPENTRAN permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

Examples

This example obtains transaction information for the current database and for the pubs database.

-- Display transaction information only for the current database.

DBCC OPENTRAN

GO

-- Display transaction information for the pubs database.

DBCC OPENTRAN('pubs')

GO

To view performance information

sysperfinfo

Contains a Microsoft® SQL Server™ representation of the internal performance counters that can be displayed through the Windows NT Performance Monitor.

Note  The Windows NT Performance Monitor is available only when using Microsoft Windows NT® 4.0 as the operating system.

Performance condition alerts are only available for the first 99 databases. Any databases created after the first 99 databases will not be included in the sysperfinfo system table, and using the sp_add_alert procedure will return an error.

To view procedure cache usage

DBCC PROCCACHE

Displays information in a table format about the procedure cache.

Syntax

DBCC PROCCACHE

Remarks

SQL Server Performance Monitor uses DBCC PROCCACHE to obtain information about the procedure cache.

Result Sets

This table describes the columns of the result set.

Permissions

DBCC PROCCACHE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

To view general statistics about SQL Server activity and usage

sp_monitor

Displays statistics about Microsoft® SQL Server™.

Syntax

sp_monitor

Return Code Values

0 (success) or 1 (failure)

Result Sets

Remarks

SQL Server keeps track, through a series of functions, of how much work it has done. Executing sp_monitor displays the current values returned by these functions and shows how much they have changed since the last time the procedure was run.

For each column, the statistic is printed in the form number(number)-number% or number(number). The first number refers to the number of seconds (for cpu_busy, io_busy, and idle) or the total number (for the other variables) since SQL Server was restarted. The number in parentheses refers to the number of seconds or total number since the last time sp_monitor was run. The percentage is the percentage of time since sp_monitor was last run. For example, if the report shows cpu_busy as 4250(215)-68%, the CPU has been busy 4250 seconds since SQL Server was last started up, 215 seconds since sp_monitor was last run, and 68 percent of the total time since sp_monitor was last run.

Permissions

Execute permissions default to members of the sysadmin fixed server role.

Examples

This example reports information about how busy SQL Server has been.

USE master

EXEC sp_monitor

Here is the result set: