INF: Cómo ver los datos de bloqueo de SQL Server 2000

Seleccione idioma Seleccione idioma
Id. de artículo: 283725 - Ver los productos a los que se aplica este artículo
Este artículo se publicó anteriormente con el número E283725
Expandir todo | Contraer todo

Resumen

En este artículo se definen vistas que pueden utilizarse para ver los datos de bloqueo contenidos en un archivo de traza creado por el trabajo descrito en el artículo siguiente de Microsoft Knowledge Base:
283696 INF: Job to Monitor SQL Server 2000 Performance and Activity
Antes de crear estas vistas, debe importar el archivo de traza a una tabla de SQL Server 2000 llamada ActivityTrace. En el artículo siguiente se define el archivo de secuencia de comandos para un procedimiento almacenado llamado trace_gettable, que importa los datos del archivo de traza a una tabla de SQL Server 2000:
283784 INF: Cómo ver los datos de actividad de SQL Server 2000
Utilice las vistas definidas a continuación para mostrar los datos de bloqueo tal y como se indica:
  • La vista v_blocked muestra los identificadores de procesos del sistema (SPID) que están bloqueados.
  • La vista v_blocking muestra todos los SPID relacionados con los bloqueos.
  • La vista v_blockspids1 muestra un resumen de los SPID que están bloqueando otros SPID. La columna RowNumber de esta vista muestra el número de fila de la tabla ActivityTrace y el archivo de traza. Utilice la consulta siguiente para ver la columna TextData de la tabla ActivityTrace para las filas anteriores a la mostrada en la vista v_blockspid1.
    select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000
    Cambie el segundo número (5000) en la consulta anterior por el valor devuelto en v_blockspid1 para RowNumber. Cambie el primer número (4080) de forma que corresponda al número de filas anteriores que desea ver. Por ejemplo, si v_blockspid1 devuelve el valor 52365 para RowNumber y desea ver las 20 filas de la tabla ActivityTrace anteriores a dicha fila, emita la instrucción siguiente:
    select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365
  • La vista v_blockspids2 muestra una lista detallada de los SPID que están bloqueando otros SPID.

Más información

CREATE view v_blocked as 
select RowNumber,StartTime
 ,TextData
 ,cast(substring(BinaryData,1,2) as smallint) as [spid]
 ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
 ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
 ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
 ,cast(substring(BinaryData,9,4) as int) as [waittime]
 ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
 ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
 ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
 ,cast(substring(BinaryData,591,2) as smallint) as [uid]
 ,cast(substring(BinaryData,593,4) as int) as [cpu]
 ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
 ,cast(substring(BinaryData,605,4) as int) as [memusage]
 ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
 ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
 ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
 ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
 ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
 ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
 ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
 ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
 ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
 ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
 ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
 ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
 ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
 ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
 ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
 ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
 from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
 and cast(substring(BinaryData,5,2) as smallint) != 0
CREATE view v_blocking as
select RowNumber,StartTime
 ,TextData
 ,cast(substring(BinaryData,1,2) as smallint) as [spid]
 ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
 ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
 ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
 ,cast(substring(BinaryData,9,4) as int) as [waittime]
 ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
 ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
 ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
 ,cast(substring(BinaryData,591,2) as smallint) as [uid]
 ,cast(substring(BinaryData,593,4) as int) as [cpu]
 ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
 ,cast(substring(BinaryData,605,4) as int) as [memusage]
 ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
 ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
 ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
 ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
 ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
 ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
 ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
 ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
 ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
 ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
 ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
 ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
 ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
 ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
 ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
 ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
 from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
create view v_blockspids1 as
select top 100 percent
 cast(substring(BinaryData,1,2) as smallint) as [spid]
 ,min(RowNumber) as RowNumber
 from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
 and cast(substring(BinaryData,5,2) as smallint) = 0
 and cast(substring(BinaryData,1,2) as smallint) in 
 (select cast(substring(BinaryData,5,2) as smallint) as spid
 from ActivityTrace
 where cast(substring(BinaryData,5,2) as smallint) != 0)
 group by cast(substring(BinaryData,1,2) as smallint)
 order by cast(substring(BinaryData,1,2) as smallint)
CREATE view v_blockspids2 as 
select RowNumber,StartTime
 ,TextData
 ,cast(substring(BinaryData,1,2) as smallint) as [spid]
 ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
 ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
 ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
 ,cast(substring(BinaryData,9,4) as int) as [waittime]
 ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
 ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
 ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
 ,cast(substring(BinaryData,591,2) as smallint) as [uid]
 ,cast(substring(BinaryData,593,4) as int) as [cpu]
 ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
 ,cast(substring(BinaryData,605,4) as int) as [memusage]
 ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
 ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
 ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
 ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
 ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
 ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
 ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
 ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
 ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
 ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
 ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
 ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
 ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
 ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
 ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
 ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
 from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
 and cast(substring(BinaryData,5,2) as smallint) = 0
 and cast(substring(BinaryData,1,2) as smallint) in 
 (select cast(substring(BinaryData,5,2) as smallint) as spid
 from ActivityTrace
 where cast(substring(BinaryData,5,2) as smallint) != 0)

Propiedades

Id. de artículo: 283725 - Última revisión: jueves, 06 de noviembre de 2003 - Versión: 4.1
La información de este artículo se refiere a:
  • Microsoft SQL Server 2000 Standard Edition
Palabras clave: 
_ik kbinfo KB283725

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com