Menu
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.
602
You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).
The best possible solution for this is the one you are using now. Since you cannot predict the number of columns you cannot set a width to each column. So setting the overflow of the parent div to auto and the child table width to 100% is the best solution.
If you really need to reduce the size of the database then you need to reduce the amount of data. And you don't want to shrink your database. Since you are on SQL 2016 SP1 you can turn on PAGE compression, or store the data in a clustered Columnstore index.
* If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.
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.
115
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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
