How to Rollover a Table
-
I am looking for some direction about how to keep a table in a SQL server db (2000 or above) of fixed size. Requirements: Keep n records, Keep t date/time range of records, keep priority records etc... a) Should I delete old records on insert, ie. stored proc? b) Should I delete old records with a Job/External process? c) Should I just use the the old "DELETE FROM tablename" command? Since I will be filling the table to it's limit often, and deleting the old contents should I clean up the transaction logs as well? When? How? I am just looking for some general direction. ............................. There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life. Fiat justitia, et ruat cælum
-
I am looking for some direction about how to keep a table in a SQL server db (2000 or above) of fixed size. Requirements: Keep n records, Keep t date/time range of records, keep priority records etc... a) Should I delete old records on insert, ie. stored proc? b) Should I delete old records with a Job/External process? c) Should I just use the the old "DELETE FROM tablename" command? Since I will be filling the table to it's limit often, and deleting the old contents should I clean up the transaction logs as well? When? How? I am just looking for some general direction. ............................. There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life. Fiat justitia, et ruat cælum
Use stored procedures to only return the set size. Depending on tps requirements you can then write a trigger or a job to run and clear the table removing old elements. I would use the job if you can get a way with it. The trigger, however, will always keep the table the right size. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Use stored procedures to only return the set size. Depending on tps requirements you can then write a trigger or a job to run and clear the table removing old elements. I would use the job if you can get a way with it. The trigger, however, will always keep the table the right size. A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
You can use the following script to keep the table 10000 rows. using Indentity
DELETE from BigTable Where ID NOT in ( SELECT Top(10000) from BigTable ORDER BY ID DESC )Please test first with a test table.
Look where you want to go not where you don't want to crash. Bikers Bible
-
You can use the following script to keep the table 10000 rows. using Indentity
DELETE from BigTable Where ID NOT in ( SELECT Top(10000) from BigTable ORDER BY ID DESC )Please test first with a test table.
Look where you want to go not where you don't want to crash. Bikers Bible
Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :) A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
-
Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :) A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Thanks all I will give the suggestions a try. ............................. There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life. Fiat justitia, et ruat cælum
-
Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :) A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Quite Quite Sure !
Look where you want to go not where you don't want to crash. Bikers Bible -- modified at 9:54 Tuesday 18th July, 2006
-
Are you sure he should first test on a test test table before testing on a test table. One can never be to careful :) A man said to the universe: "Sir I exist!" "However," replied the Universe, "The fact has not created in me A sense of obligation." -- Stephen Crane
Please call me skippy
Look where you want to go not where you don't want to crash. Bikers Bible
-
You can use the following script to keep the table 10000 rows. using Indentity
DELETE from BigTable Where ID NOT in ( SELECT Top(10000) from BigTable ORDER BY ID DESC )Please test first with a test table.
Look where you want to go not where you don't want to crash. Bikers Bible