I am using SQL Server 2008 R2. I have created a database named testDB. I have a lot of tables with some log tables in. Some log tables contain over 100k records.

I want to fix the size of those log tables, and want to move records into another database table in another location, so my database has no problem.

Is there any way to make such above steps which I want for my database?

Is there already built any such functionality in SQL Server?


There isn't a standard option to do this, but you can achieve this several ways.

  1. create a separate db and move your log tables there. set a max file size.
  2. create a trigger which checks row count or table size and limits inserts when value exceeded and/or deletes old data.
  3. create a foreign key constraint on an id column pointing to a table with a set number of rows. When your log table reaches the maximum it will stop.
  4. add a check constraint.
  • thanks for your suggestion, I will try and let you know. – Ghost Answer Apr 23 '15 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.