两个sql server存储过程，一个触发器

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