SQL Server Performance Forum – Threads Archive
always keep three years record in the table
how can i set a DTS, Trigger or Store Procedure to perform the process of auto delete a record in the table with the condition of: – only store three year record in the table by auto check with the system year minus 3year.
– the table will content of 2 million row record. and i also facing the problem of DTS uploading process is too long (one table with 2 million record and every time it need to take about 30 minutes to upload the data from text file. How can i speed up the DTS process?? Thank you!
Uploading 2+ million rows execution time is expected slow and it depends on the network availability and other resources accessing SQL server during that job, and one of the MS article refers:
To maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs. For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods. Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage. Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule. … try it and see. You can define your query to delete the rows by using DTS to a temp table and then transfer those rows to the original table. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya hoow do we do this? I never encountered this before. As for the original question, rather than cleaning up using a trigger, I would recommend having a daily job which checks for the conditions and acts accordingly. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
BOL bols:
To set CPU idle time and duration Expand a server group, and then expand a server.
Expand Management, right-click SQL Server Agent, and then click Properties.
Click the Advanced tab, and then select the The computer is idle when check box.
Under Idle CPU condition, do the following:
Specify the percentage that the average CPU usage must remain below (across all CPUs).
Specify the duration in seconds before the computer is considered idle.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>