Info: How to SQL Server 2000 blockieren Seriendruck

Wichtig: Dieser Artikel wurde maschinell und nicht von einem Menschen übersetzt. Die Microsoft Knowledge Base ist sehr umfangreich und ihre Inhalte werden ständig ergänzt beziehungsweise überarbeitet. Um Ihnen dennoch alle Inhalte auf Deutsch anbieten zu können, werden viele Artikel nicht von Menschen, sondern von Übersetzungsprogrammen übersetzt, die kontinuierlich optimiert werden. Doch noch sind maschinell übersetzte Texte in der Regel nicht perfekt, insbesondere hinsichtlich Grammatik und des Einsatzes von Fremdwörtern sowie Fachbegriffen. Microsoft übernimmt keine Gewähr für die sprachliche Qualität oder die technische Richtigkeit der Übersetzungen und ist nicht für Probleme haftbar, die direkt oder indirekt durch Übersetzungsfehler oder die Verwendung der übersetzten Inhalte durch Kunden entstehen könnten.

Den englischen Originalartikel können Sie über folgenden Link abrufen: 283725
Dieser Artikel wurde archiviert. Er wird im vorliegenden Zustand bereitgestellt und nicht mehr aktualisiert.
Zusammenfassung
Dieser Artikel definiert Ansichten, die zum Anzeigen von Sperren in eine Ablaufverfolgung-Datei, die von der im folgenden Artikel in der Microsoft Knowledge Base beschriebenen Auftrag erstellt wird enthaltenen Daten verwendet werden können:
283696Info: Projekt überwachen SQLServer 2000-Leistung und Aktivität
Bevor Sie diese Ansichten erstellen, muss die Ablaufverfolgungsdatei in eine SQL Server 2000-Tabelle mit dem Namen ActivityTrace importiert werden. Das Skript für eine gespeicherte Prozedur aufgerufen Trace_gettable , die die Daten aus der Ablaufverfolgungsdatei zu einer SQL Server 2000-Tabelle importiert wird im folgenden Artikel definiert:
283784Info: How to SQL Server 2000-Aktivität Seriendruck
Verwenden Sie die Ansichten, die zum Anzeigen von blockierender Daten wie erwähnt unten definiert sind:
  • Die V_blocked-Ansicht Listet die Systemprozess-IDs (SPIDs), die blockiert werden.
  • Die Ansicht V_blocking Listet alle die SPIDs blockiert beteiligt.
  • Die v_blockspids1-Ansicht ist eine zusammenfassende Liste der SPIDs, die andere Spids blockiert werden. Die RowNumber-Spalte in dieser Ansicht ist die Nummer der Zeile aus der ActivityTrace-Tabelle und Trace-Datei. Verwenden Sie die folgende Abfrage, um die TextData-Spalte aus der Tabelle ActivityTrace für Zeilen vor, die Zeile aufgeführt, in der v_blockspid1-Ansicht anzeigen.
    select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000						
    ändern Sie die zweite Zahl (5000) in der obigen Abfrage auf den Wert, für die Zeilenzahl in v_blockspid1 zurückgegeben. Ändern Sie die erste Zahl (4080) entsprechend der vorherigen Zeilenanzahl, die Sie anzeigen möchten. Beispielsweise wenn v_blockspid1 Zeilenzahl 52365 gibt, und Sie die 20 Zeilen aus der Tabelle ActivityTrace vor dieser Zeile anzeigen möchten, die folgende Anweisung ausgeben:
    select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365					
  • Die v_blockspids2-Ansicht ist eine detaillierte Liste der SPIDs, die andere SPIDs blockiert werden.
Weitere Informationen
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)				

Warnung: Dieser Artikel wurde automatisch übersetzt.

Eigenschaften

Artikelnummer: 283725 – Letzte Überarbeitung: 01/16/2015 21:22:26 – Revision: 3.2

Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbmt kbinfo KB283725 KbMtde
Feedback