Project

General

Profile

Дополнительный материал » mdb_clean.sql

Sergey Kolyshkin, 10/01/2023 10:29

 
/* Delete all news items older than XX days
It should be done first*/

print 'Delete all news items older than XX days...'

begin transaction

DECLARE @id_to_clean table(id int NOT NULL);

insert into @id_to_clean
select ph.id
from attrib_vals
left outer join ph_to_attrvals on attrib_vals.id = ph_to_attrvals.attrval_id
left outer join ph on ph.id = ph_to_attrvals.ph_id
where
attrib_vals.deleted=0 and attrib_vals.type = 5
and attrib_vals.attr_id=1 and attrib_vals.ForNewsBrowser=1
and ph.deleted=0 and ph.AddDate<(dateadd( day, -14, CURRENT_TIMESTAMP))

ALTER TABLE [PH_TO_ATTRVALS] DISABLE TRIGGER OnPhToAttrVals
ALTER TABLE [ph] DISABLE TRIGGER ModifyPH

/* Clear all attribute links for deleted items (#3730)*/
DELETE FROM [PH_TO_ATTRVALS] WHERE Ph_ID IN (select * from @id_to_clean)
update ph set deleted=1, modifydate=CURRENT_TIMESTAMP where id in(select * from @id_to_clean)

ALTER TABLE [PH_TO_ATTRVALS] ENABLE TRIGGER OnPhToAttrVals
ALTER TABLE [ph] ENABLE TRIGGER ModifyPH

commit transaction

GO


/*Delete lost attrib links*/
print 'Delete lost attrib links...'

begin transaction

ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

delete from ph_to_attrvals where id in
(select distinct ph_to_attrvals.id from ph_to_attrvals, attrib_vals
where
attrib_vals.id = ph_to_attrvals.attrval_id and
attrib_vals.deleted = 1)

delete from ph_to_attrvals where id in
(select distinct ph_to_attrvals.id from ph_to_attrvals, ph
where
ph.id = ph_to_attrvals.ph_id and
ph.deleted = 1)

ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

commit transaction
GO


/*Clear recycle bin*/
print 'DELETE FROM PH...'
begin transaction

ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

DELETE FROM PH where deleted=1 AND modifydate<(dateadd( day, -7, CURRENT_TIMESTAMP)) AND 0 = (select count(*) from mp_phonograms where ph_id=PH.id)

ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

commit transaction
GO

/*Clear recycle bin*/
print 'DELETE FROM ATTRIB_VALS...'
DELETE FROM ATTRIB_VALS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear recycle bin*/
print 'DELETE FROM CAT_JNGL_BTNS...'
DELETE FROM CAT_JNGL_BTNS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear Old Scheule elems*/
print 'DELETE FROM PLIST_ELEMS...'
DELETE FROM PLIST_ELEMS where not exists(select id from PLIST_BLOCKS where id = PLIST_ELEMS.BlockID)
GO

/*Clear Old Scheule elems*/
print 'DELETE FROM PLIST_ELEMS...'
DELETE FROM PLIST_ELEMS
where BlockID in(select ID FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP)))
GO

/*Clear Old Scheule Blocks*/
print 'DELETE FROM PLIST_BLOCKS...'
DELETE FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP))
GO

/*Clear Old Changes*/
print 'DELETE FROM MODIFY_DATES...'
DELETE FROM MODIFY_DATES where modifydate< (dateadd( day, -1, CURRENT_TIMESTAMP))
GO

/*Clear sch_files*/
print 'DELETE FROM SCH_FILES...'
DELETE FROM SCH_FILES where SchDate < (dateadd( day, -3, CURRENT_TIMESTAMP))
GO

/*Delete old DDB blocks info*/
print 'Delete old DDB blocks info...'
DELETE FROM P_BLOCKS where BlkDate < (dateadd( day, -3, CURRENT_TIMESTAMP))
GO

/*Delete playback history older than 100 days*/
print 'Delete playback history older than 100 days...'
DELETE FROM PH_PLAY_HISTORY where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP))
GO

/*Delete plan history older than 100 days*/
print 'Delete plan history older than 100 days...'
DELETE FROM PH_PLAY_PLAN where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP))
GO

/* Delete old MAG2 shedule orders*/
print 'Delete old mag schedule orders'
DELETE FROM [dbo].[MAG2_WORK] where WorkDate< dateadd( day, -14, GETDATE())
GO



/*Delete PH_HISTORY rows older than 30 days*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PH_HISTORY]') AND type in (N'U'))
BEGIN
PRINT N'Delete PH_HISTORY rows older than 30 days'
DECLARE @MinDate datetime
SELECT @MinDate=dateadd(d,-30, GETDATE())
DELETE
FROM [dbo].[PH_HISTORY]
WHERE ModifyDate < @MinDate
END

GO



/*Truncating transaction log file for current database*/
print 'Truncating transaction log file for current database...'

declare @ver int
declare @ver_str varchar(128)
set @ver_str = convert(varchar(128),SERVERPROPERTY('ProductVersion'))
set @ver = cast( left(@ver_str, charindex('.', @ver_str)-1) as int )

declare @db_name varchar(128),
@dbcc_query varchar(256)

SELECT @db_name = DB_NAME()

if (@ver < 10)
BEGIN
set @dbcc_query='BACKUP LOG ['+@db_name+'] WITH TRUNCATE_ONLY'
EXEC (@dbcc_query)
END

set @dbcc_query='DBCC SHRINKDATABASE (['+@db_name+'], 0)'
EXEC (@dbcc_query)
GO

(24-24/25)
Add picture from clipboard (Maximum size: 742 MB)