Al momento sei offline in attesa che la connessione Internet venga ristabilita

Come monitorare i blocchi in SQL Server 2005 e SQL Server 2000

Il presente articolo è stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell’utente. Tuttavia, un articolo tradotto in modo automatico non è sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, più o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non è la sua. Microsoft non è responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell’utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.

Clicca qui per visualizzare la versione originale in inglese dell’articolo: 271509
Sommario
Se si utilizza Microsoft SQL Server 2000, è necessario applicare SQL Server 2000 Services Pack 3a (SP3a) prima di utilizzare lo script è descritto in questo articolo.

Per ulteriori informazioni su come ottenere SQL Server 2000 Service Pack 3a, fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:
290211Come ottenere il service pack più recente per SQL Server 2000
In questo articolo è un aggiornamento per SQL Server 2000 o versioni successive di SQL Server di il seguente articolo della Microsoft Knowledge Base, che si applica a Microsoft SQL Server 7.0:
251004 Come monitorare i blocchi 7.0 SQL Server
Questo articolo viene descritto l'utilizzo e la progettazione di un stored procedure che è possibile utilizzare per diagnosticare problemi di blocco e prestazioni. Per una descrizione delle modalità di risoluzione dei blocchi, vedere il seguente articolo della Microsoft Knowledge Base:
224453 Comprensione e la risoluzione SQL Server 7.0 o 2000 i problemi di blocco
In Microsoft SQL Server 2005, è inoltre possibile utilizzare il Report processo bloccato classe di evento SQL Server Profiler per acquisire le informazioni relative a un'attività che è stata bloccata per più di un periodo di tempo specificato.

Per ulteriori informazioni sui Report processo bloccato evento di classe, visitare il seguente sito Web Microsoft Developer Network (MSDN):
Informazioni
La seguente descrizione del sp_blocker_pss08 stored procedure consente di catturare queste informazioni. Queste informazioni si applicano anche a SQL Server 2005.
  • L'ora di inizio (in base al computer in cui esecuzione di SQL Server) in modo che il campionamento del blocco può essere allineato ad altre informazioni sulle prestazioni, ad esempio delle prestazioni di Microsoft Windows NT Log di monitor o un Profiler di SQL.
  • Informazioni sulle connessioni a SQL Server, eseguendo una query Verrà visualizzata la finestra di dialogo sysprocesses tabella di sistema.
  • Informazioni sulle risorse di blocco, interrogando il syslockinfo tabella di sistema.
  • Informazioni sulla risorsa in attesa, mediante l'esecuzione di DBCC SQLPERF (WAITSTATS).
  • Corrente in esecuzione di Batch di SQL Server per le connessioni che sono bloccate da altre che bloccano altre, mediante l'esecuzione di DBCC INPUTBUFFER istruzione.
  • L'ora di fine, in base al computer su cui è in esecuzione SQL Server.
La stored procedure è stata creata con il seguente codice ottimizzazioni per ridurre l'effetto sulle prestazioni e blocco eseguendo questo stored procedure:
  • Non vi è alcun output generato, a meno che almeno una connessione è in attesa di una risorsa.
  • Il sysprocesses e syslockinfo tabelle di sistema al master database viene chiesto direttamente, per migliorare le prestazioni e a impedire il blocco di questa stored procedure. Pertanto, questo archiviati procedura è specifica per Microsoft SQL Server 2000 o versioni successive di SQL Server.
  • Una piccola tabella di lavoro viene creato il cursore per ottenere il Output di DBCC INPUTBUFFER; non dovrebbe avere alcun effetto in quanto l'utilizzo nel tempdb database.
  • Poiché il blocco può cambiare durante la raccolta di esiste una modalità rapida di informazioni, che migliora le prestazioni riducendo il i risultati alle righe rilevanti del sysprocesses e syslockinfo tabelle di sistema.
  • Se desideri tenere traccia di risorsa di blocco non attese, una modalità di latch esiste, causando il blocco di output per essere omesso.
Questa stored procedure è utile eseguire da qualsiasi query strumento. Tuttavia, Microsoft suggerisce di eseguire la procedura per eseguire analisi dei blocchi:
  1. Creare la stored procedure sp_blocker_pss08, incluso alla fine del presente articolo, da qualsiasi query strumento mentre si è connessi con un account di accesso è sysadmin privilegi sul server SQL Server o l'istanza di SQL Server si desidera monitorare.
  2. Creare un file di script con la seguente query per eseguire il stored procedure in un ciclo. Si noti che il ritardo deve essere compreso tra 5 e 60
    secondi:

    MENTRE 1 = 1
    INIZIARE
    EXEC tempdb.dbo.sp_blocker_pss08
    - O per la modalità veloce
    -- EXEC tempdb.dbo.sp_blocker_pss08 @ fast = 1
    - O per la modalità di latch
    -- EXEC tempdb.dbo.sp_blocker_pss08 @ latch = 1
    WAITFOR DELAY ' 00: 00:15'
    FINE
    VAI
  3. Questo output è molto utile se combinato con un Microsoft Registro Windows NT Performance Monitor e un log SQL Profiler, la creazione di entrambi su si consiglia di utilizzare la stessa ora. Per informazioni riguardanti i Profiler e Gli eventi di monitoraggio delle prestazioni per acquisire e per informazioni su come interpretare i risultati, vedere il seguente articolo della Microsoft Knowledge Base:
    224453Comprensione e la risoluzione SQL Server 7.0 o 2000 i problemi di blocco
  4. Eseguire il file di script creato nel passaggio 2 da isql, lo strumento di query osql. exe o l'utilità Sqlcmd in un prompt dei comandi di Windows nel computer in cui è in esecuzione di SQL Server per il quale si desidera monitorare per evitare problemi di reti da disconnettere lo strumento di query. Di seguito è una riga di comando di esempio che è possibile utilizzare per l'avvio di osql. exe, si presuppone che il client viene eseguito dal computer che esegue SQL Server, e che il nome del file di script sia Checkblk. SQL. Assicurarsi di correggere il parametro -S e sostituire "server" con il nome del server SQL Server (o "servername\instance.") se si sta monitorando un'istanza denominata. Anche correggere -i parametri e Checkblk. replace "SQL" con il percorso e il nome del file di script creato nel passaggio 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    Si noti che è necessario utilizzare le altre opzioni della riga di comando per il motivi seguenti:

    • Per impedire il ritorno a capo alla riga nell'output del file che facilita la lettura.
    • Per inviare l'output in un file specificato con -o parametro anziché sullo schermo in modo che, se lo strumento di query presenta problemi, si ancora esportato fino a quando lo strumento di query avrà esito negativo.
Di seguito è riportato lo script per creare il sp_blocker_pss08 stored procedure:
/*Note: This script is meant to have 3 creations of the same stored procedure and two of them will failwith either 207 errors or a 2714 error.*/use masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss08GOcreate procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.2005 - 2005 or Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantdeclare @request varchar(12)set @time = getdate()declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, request_id, ecid))insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1)begin   set @time2 = getdate()   print ''   print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end, request_id      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where blocked != 0)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select distinct spid from @probclients -- change: added distinct            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid               --and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end, request_id      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'sys.dm_os_wait_stats'   select * from sys.dm_os_wait_stats where waiting_tasks_count > 0   print 'OWS'   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id   from @probclients   where (spid <> @@spid) and (spid > 50) and      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid, @request   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'      exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')      fetch next from ibuffer into @spid, @request   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '      + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'GO    create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.20003 --2000 SP3 or Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @time = getdate()declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, ecid))insert @probclients select spid, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)begin   set @time2 = getdate()   print ''   print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.20003 ' +ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where blocked != 0)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'DBCC SQLPERF(WAITSTATS)'   dbcc sqlperf(waitstats)   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid   from @probclients   where (spid <> @@spid) and       ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid      exec ('dbcc inputbuffer (' + @spid + ')')      fetch next from ibuffer into @spid   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '      + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.20003'GO    create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.2000 -- 2000 before SP3if is_member('sysadmin')=0begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @time = getdate()declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, ecid))insert @probclients select spid, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)begin   set @time2 = getdate()   print ''   print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2000 '+ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where waittype != 0x0000)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'DBCC SQLPERF(WAITSTATS)'   dbcc sqlperf(waitstats)   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid   from @probclients   where (spid <> @@spid) and       ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid      exec ('dbcc inputbuffer (' + @spid + ')')      fetch next from ibuffer into @spid   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'GO 
Per ulteriori informazioni, visitare il seguente sito Web:

Avviso: questo articolo è stato tradotto automaticamente

Proprietà

ID articolo: 271509 - Ultima revisione: 07/29/2011 07:54:00 - Revisione: 1.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Workgroup Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition

  • kbsqlsetup kbhowto kbinfo kbmt KB271509 KbMtit
Feedback