INF: ????? ?????? ??????? SQL Server 7.0

?????? ????????? ?????? ?????????
???? ???????: 251004 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

??????

????????? ??? ??????? ??????? ?????? ??????? ?????? ???? ????????? ?????? ???????? ??? ???????. ?????? ??? ??? ?? ????? ??? ??? ??? ???? ??????? ??????? ?? "????? ??????? ?? Microsoft:
224453INF: ??? ?????? ?? ????? ??? SQL Server 7.0
?????? ??? ??????? ??? ??? ???????? ??? ?? SQL Server 2000 ???? ??????? ??????? ?? "????? ??????? ?? Microsoft:
271509INF: ????? ?????? ??????? SQL Server 2000

??????? ????

????? ??????? sp_blocker_pss70 ????? ?????? ?? ??? ?????? ????????? ???????:
  • ??? ????? (???????? ??? ????????? ???? ???? ?????? SQL Server) ???? ???? ??? ??????? ??? ?? ??? ?????? ????? ?? ??????? ???? ?????? "? ??? ??? ????? ???? Windows NT Microsoft ?? ??? ???? ????? ??????? SQL.

  • ??????? ??? ??????? ?? SQL Server ?????? ????????? ?? ???? ?????? sysprocesses.

  • ??????? ??? ????? ??????? ?? ???? ????????? ?? ???? ?????? syslockinfo.

  • ??????? ??? ?????? ?????? ?? ???? ????? DBCC SQLPERF(WAITSTATS).

  • ?????? ??? ??????? SQL ????????? ???? ??? ????? ?????? ??????? ?? ??? ??????? ?? ???? ????? INPUTBUFFER DBCC.

  • ?????? ????? ???????? ??? ??????? ???? ????? ??????? SQL Server.
?? ????? ????? ???? ?? ??????? ??????? ?????? ??????? ??? ???? ? ??? ?????? ????? ??? ??????? ??????:
  • ?? ???? ?? ????? ????? ?? ?? ??? ?????? ????? ???? ??? ????? ??? ??? ?????.

  • ?????? ????? ?????? sysprocesses ? syslockinfo ?? ????? ???????? ???????? ???????? ?????? ?????? ????? ??? ??????? ?????? ???? ???. ????? ???? ??? ??????? ?????? ???? ?? Microsoft SQL Server 7.0.

  • ??? ????? worktable ???? ?????? ??? ?????? ?????? ??? ????? INPUTBUFFER DBCC; ????? ??? ?? ????? ??????? ??? ????????? ?? ????? ?????? tempdp.

  • ????? ???????? ????? ??? ????? ????? ????????? ????? ??? ???? ???? ????? ?????? ?? ???? ????? ??????? ??? ???? ????? ?????? sysprocesses ? syslockinfo ??? ?????.

  • ??? ????? ???? ????? ????? - ????? ??????, ???? ??? ????? ???? ???? ????? ??????? ??? ?? ??? ???.
???? ??? ??????? ?????? ?????? ????? ?? ??? ???? ?????????. ??? ???? ????? Microsoft ???? ??????? ??????? ??? ?????? ?????? ???:
  1. ????? ?? ??????? ?????? sp_blocker_pss70 ? ???? ??? ?????? ?? ????? ??? ??????? ?? ??? ???? ????????? ????? ????? ????? ???? ???? ???????? ????? ?????? ??? ???? SQL Server ?? ???? SQL Server ???? ???? ???????.
  2. ????? ??? ???????? ??? ?? ????????? ?????? ?????? ??????? ?????? ?? ????. ???? ?? ??? ??????? ??? 5 ? 60 ?????:
    WHILE 1=1
    BEGIN
       EXEC master.dbo.sp_blocker_pss70
       -- Or for fast mode 
       -- EXEC master.dbo.sp_blocker_pss70 @fast=1
       -- Or for latch mode 
       -- EXEC master.dbo.sp_blocker_pss70 @latch=1
       WAITFOR DELAY '00:00:15'
    END
    GO
    					
  3. ????? ??? ?????? ????? ??? ????? ?? ??? ????? ???? Windows NT Microsoft ?????? SQL ???? ????? ??????? ? ??? ????? ?????? ?? ??? ????? ??? ???????. ?????? ??? ??????? ????? ??????? ???? ???? ????? ??????? "?" ?????? ?????? "??????? ?????? ??? ??????? ??? ????? ????? ??????? ???? ??????? ??????? ??" ????? ??????? ?? Microsoft:
    224453INF: ??? ?????? ?? ????? ??? SQL Server 7.0
  4. ????? ??? ???????? ????? ???? ?? ?????? ?? ?????? 2 ?? ??? Isql.exe ?? Osql.exe ????????? ?????? ?? ???? ????? Windows ??? ??????? ???? ????? ??????? SQL Server ???? ???? ?? ?????? ???? ????? ???? ??????? ?? ??? ????? ?????? ?????????. ???? ?????? ??? ????? ???? ???? Osql.exe ????? ????? ?????? ?? ???? ??????? ???? ????? ??????? SQL Server ???? ??? ???????? ????? Checkblk.sql. ???? ?? ?? ????? S ???????-, ?????? "??????" ??? ???? SQL ?????? ?????? ??????? &-i, ?????????? "checkblk.sql" ?? ???? ???? ??? ???????? ????? ??????? ?? ?????? 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    						
    ????? ?????? ??? ???? ??????? ??? ??????? ?????? ??????? ???????:

    • ???? ?????? ????? ?? ????? ??????? ??? ???? ???? ?? ???????.
    • ?????? ??????? ??? ??? ???? ????????-o ? ????? ?? ?????? ???? ??? ??? ?????? ????????? ????? ?? ???? ???? ????? ??? ???? ???? ?????????.
?????? ?? ?????? ??? ????? ????????? ?? ????? ??????? sp_blocker_pss70 ???????:

use master
GO

if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss70
GO

create proc sp_blocker_pss70 (@latch int = 0, @fast int = 1)
as 
--version 13
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @time3 datetime
declare @dbname nvarchar(128)
declare @IsDetached tinyint
declare @IsEmergencyMode tinyint
declare @IsInLoad tinyint
declare @IsInRecovery tinyint
declare @IsNotRecovered tinyint
declare @IsOffline tinyint
declare @IsShutDown tinyint
declare @IsSingleUser tinyint
declare @IsSuspect tinyint

set @time = getdate()

select spid, ecid, blocked, waittype, dbid, 'ignore_app' = case 
  when convert(varchar(128),hostname) = 'PSSDIAG' then 1 else 0 end into #probclients
  from master..sysprocesses where blocked!=0 or waittype != 0x0000
create unique clustered index pc on #probclients (blocked, spid, ecid)
set @time3 = getdate()

if exists (select spid from #probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '7 Start time: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,@time2)) + ' ' + convert(varchar(12), datediff(ms,@time,@time3))

   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..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'
            set @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' 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 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            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 -- blocking via locks
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
   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..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'
            set @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' 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 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            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)

   end -- Fast not set

   print ''
   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)
   print ''

   print ''
   Print '*********************************************************************'
   Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   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, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid, @blocked
   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)
      select @IsDetached = DATABASEPROPERTY(@dbname,'IsDetached')
      select @IsEmergencyMode = DATABASEPROPERTY(@dbname,'IsEmergencyMode')
      select @IsInLoad = DATABASEPROPERTY(@dbname,'IsInLoad')
      select @IsInRecovery = DATABASEPROPERTY(@dbname,'IsInRecovery')
      select @IsNotRecovered = DATABASEPROPERTY(@dbname,'IsNotRecovered')
      select @IsOffline = DATABASEPROPERTY(@dbname,'IsOffline')
      select @IsShutDown = DATABASEPROPERTY(@dbname,'IsShutDown')
      select @IsSingleUser = DATABASEPROPERTY(@dbname,'IsSingleUser')
      select @IsSuspect = DATABASEPROPERTY(@dbname,'IsSuspect')

      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @IsDetached + @IsEmergencyMode + @IsInLoad + @IsInRecovery + @IsNotRecovered + @IsOffline
         + @IsShutDown + @IsSingleUser + @IsSuspect = 0
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=ID=' + str(@IsDetached,1) + ' IEM=' + str(@IsEmergencyMode,1) + ' IIL=' 
           + str(@IsInLoad,1) + ' IIR=' + str(@IsInRecovery,1) + ' INR' + str(@IsNotRecovered,1) + ' IO=' 
           + str(@IsOffline,1) + ' ISD=' + str(@IsShutDown,1) + ' ISU=' + str(@IsSingleUser,1) + ' IS='
           + str(@IsSuspect,1) 
      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 ''
   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
    + convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')
GO 
	

???????

???? ???????: 251004 - ????? ??? ??????: 01/???/1425 - ??????: 3.2
????? ???
  • Microsoft SQL Server 7.0 Standard Edition
????? ??????: 
kbmt kbhowto kbinfo kbsqlserv700 KB251004 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????251004

????? ???????

 

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