How to limit the size of a table?

Question

I have a SQL Server 2005 database. I am logging data to a table. I want to prevent the table data from getting too big.

How can I limit the size of the table to x number of rows, and keep logging? I want the oldest rows to drop off.

Answer 1

You have to build this process yourself. You might want to look into creating a SQL Server job that runs a SQL DELETE statement that is based on the criteria you defined.

Answer 2

This is the one example where triggers might actually be a good idea in Sql Server. (My personal feeling is that triggers in SQL are like GOTOs in code.)

Just write an INSERT trigger which, when triggered, will check for the number of rows in the file and execute a DELETE according to whatever rules you specify.

Here's a link to trigger basics. And another, this time with screen caps.

Answer 3

Place the table on its own filegroup. Limit the size of the filegroup. See:

• Using Files and Filegroups to Manage Database Growth
• How to: Move an Existing Index to a Different Filegroup

Then add a job that deletes the old log records, but this is usually trickier than it sounds. The most efficient way to use a sliding window: How to Implement an Automatic Sliding Window in a Partitioned Table. If not possible, then the next best thing is to make sure the clustered key on the table is the date, so that deletes can remove efficiently the old rows.

Answer 4

If you want to restrict the size of a table for logging purposes, I would not advise thinking of solving the problem by limiting the number of records stored in a table. Instead have an archive or purge process for the table which stores the logs, this process can be configured to either purge/archive the logs either once X number of rows is reached, or perhaps later you want to reconfigure it for after X number of min’s/hrs/etc. If you are concerned about the actual space, then it would be best to analyze how much space your logs are actually taking up. Once you have an idea how much physical space you have available for the database, then restrict the data growth from SQL Server to be sure that the data file which the logging information is stored on does not exceed your expectations.