Галактика в вопросах и ответах

Код: Выделить всё

------------------------------------
-- Запуск как 
-- Exec p_integrity_clear_journal  0
------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AlexFDateToInt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[AlexFDateToInt]
GO

CREATE FUNCTION AlexFDateToInt  (@dd datetime)
RETURNS int
AS
BEGIN
   DECLARE @id int
   SET @id = convert(integer,( + convert(binary(2), year(@dd)) + convert(binary(1), month(@dd)) + convert(binary(1), day(@dd)) ))
   RETURN(@id)
END

--End----------------------------------------------------------------------------
GO


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

GO

-- Процедура для быстрой очистки журнала  LastDate datetime)
CREATE PROCEDURE dbo.p_integrity_clear_journal  ( @dd integer )
AS 
SET NOCOUNT ON 

DECLARE  @TableCode integer, 
   @TableName sysname, 
   @execsql nvarchar(1000) 

DECLARE   @LastDate integer

--SET XACT_ABORT ON 
SET @LastDate = dbo.AlexFDateToInt(dateadd( day, @dd * (-1), getdate()) )

-- SET @LastDate=ISNULL(@LastDate, CAST('99991231' as datetime))  -- очищаем весь журнал, если передан NULL 

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
--BEGIN TRAN 

-- Блокируем удаляемые записи 
--SELECT @TableCode=MAX(TableCode) FROM X$JOURNAL WHERE STATUS IN (0,1,2,3) AND LASTDATE<=@LastDate 
-- begin
--IF @TableCode IS NULL  -- нет записей в журнале 
--  RETURN 
DECLARE JNRecCursor CURSOR LOCAL FAST_FORWARD 
FOR SELECT DISTINCT TableCode FROM X$JOURNAL WHERE STATUS IN (0,1,2,3) AND LASTDATE<=@LastDate group by TableCode

OPEN JNRecCursor 

WHILE 1=1 
BEGIN 
  FETCH NEXT FROM JNRecCursor INTO @TableCode 
  
  IF @@FETCH_STATUS<>0 
    BREAK 

  SET @TableName = 'J$'+CONVERT(VARCHAR(32),@TableCode) 
  SET @execsql = 'DELETE FROM '+@tablename+' WHERE J#NRec IN (SELECT NREC FROM X$JOURNAL WHERE STATUS IN (0,1,2,3) AND LASTDATE<='+str(@LastDate)+' AND TableCode='+str(@TableCode)+')' 
 EXEC sp_executesql @execsql
--, '@LastDate Interger, @TableCode int', @LastDate, @TableCode 
END 

CLOSE JNRecCursor 
DEALLOCATE JNRecCursor 

ALTER TABLE X$JOURNAL DISABLE TRIGGER X$JOURNAL_D 
DELETE FROM X$JOURNAL WHERE STATUS IN (0,1,2,3) AND LASTDATE<=@LastDate 
ALTER TABLE X$JOURNAL ENABLE TRIGGER X$JOURNAL_D 



--end
--COMMIT TRAN 



GO
--End----------------------------------------------------------------------------
GRANT  EXECUTE  ON dbo.p_integrity_clear_journal TO public
GO