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

Este artículo se publicó anteriormente con el número E283725
Este artículo se ha archivado. Se ofrece "tal cual" y no se volverá a actualizar.
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 asselect 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 asselect 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: 01/16/2015 21:22:29 - Revisión: 4.1

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive _ik kbinfo KB283725
Comentarios