1. 备份日志, 避免日志过快增长 no_log / trancate_only BACKUP LOG realnew_DATA WITH NO_LOG DBCC SHRINKDATABASE(realnew_DATA ,TRUNCATEONLY) BACKUP LOG realnew_DATA WITH Truncate_ONLY BACKUP LOG sjyh WITH NO_LOG DBCC SHRINKDATABASE(sjyh,TRUNCATEONLY) BACKUP LOG sjyh WITH Truncate_ONLY 2. 维持历史库数据表中的记录不超过5000条, 并将实时库中数据插入历史库 1) 使用存储过程 --作业 CREATE procedure insert_calHistory as if ((select count(tag_id) from CalcOUT_1_HIS)>3000) begin delete from CalcOUT_1_HIS where tag_id in (select top 500 tag_id from CalcOUT_1_HIS order by tag_id) end insert into CalcOUT_1_HIS select * from CalcOUT_1_REAL go 然后,添加作业 调度:每天每隔3分钟执行一次 SQL: exec insert_calHistory 2) 使用触发器--针对表一级的 CREATE TRIGGER deleterecord ON [dbo].[testtrigger] after INSERT AS IF (SELECT COUNT(*) FROM testtrigger) >5000 BEGIN DELETE FROM testtrigger where id not in ( select top 300 id from testtrigger order by id desc) END |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 17:39 , Processed in 0.196209 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.