|
/* 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
|
|
|
