How to automate deletion of oldest rows when table reaches certain number of rows?

[weeodett]

Is there a way to automate the deletion of the oldest rows in a transaction log file when the file reaches a certain number of rows? Currently, we are simply deleting the oldest rows manually every so often. But we would like to put something in place that will do this automatically. The reason for doing this is to ultimately improve the application's performance that writes the transactions to this file. When the file gets to be over about 60,000 rows, performance of the application seems to degrade.

[iburyak]

Do you have unique ID in this table?

[weeodett]

Yes, there is a unique RecordID

[iburyak]

1. Create test environment

[PHP]select * into #temp_table from log_table [/PHP]

2. [PHP]Select * from #temp_table where ID not in (Select Top 60000 ID from #temp_table order by ID desc) [/PHP]
-- you can use date or other order criteria make sure it is desc. The idea is to select last 60000 records and exclude them from delete
-- Check if these are the records you want to delete.
3. [PHP]Delete from #temp_table where ID not in (Select Top 60000 ID from #temp_table order by ID desc)[/PHP]
4. Move everything in production if it worked to your satisfaction.


Godd Luck.

[weeodett]

Thank you, iburyak. I am truly a novice at SQL. What do I need to do next in order to get this to then delete the records from the original table, and automate this?

[iburyak]

Did you test delete? Did it work as you expected?

[weeodett]

I just tested the selects you sent, and yes, they did work. But I don't know how to then delete the records?

[iburyak]

How often do you need to do it?

[iburyak]

Do you have access to SQL Server Enterprize Manager?
Can you see your Server name there?

[weeodett]

Yes, I can get to Ent Mgr. I believe this should be done daily.

[iburyak]

Go to Ent Manager.
Open your server.
Go to Management - SQL Server Agent - Jobs
Right click on Jobs and select New Job....

Give it any name you want like PurgeLog
go to Steps and click New. Put delete statement there.
go to Schedules click New Schedule, give it a name and click Change.
Select how often you want to do it and save everything.

Now you all set. You will see your job name, you can right click on it and start right away or wait for scheduled time and View Job history when you want to check if job is running every time successfully.

Good luck.

[weeodett]

GREAT!!! Thanks so much for your help!!!!

[iburyak]

You are welcome.... :)