如何限制表的大小？
我有一个 SQL Server 2005 数据库。我正在将数据记录到表中。我想防止表数据变得太大。
如何将表的大小限制为 x 行数并继续记录？我希望最旧的行被删除。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您必须自己构建这个过程。您可能想要考虑创建一个 SQL Server 作业，该作业根据您定义的条件运行 SQL
DELETE语句。You have to build this process yourself. You might want to look into creating a SQL Server job that runs a SQL
DELETEstatement that is based on the criteria you defined.这是触发器在 Sql Server 中实际上可能是一个好主意的一个示例。 （我个人的感觉是，SQL 中的触发器就像代码中的 GOTO。）
只需编写一个 INSERT 触发器，该触发器在触发时将检查文件中的行数并根据您指定的任何规则执行 DELETE。
以下是触发器基础知识的链接。 和< a href="http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/" rel="nofollow">另一个，这个带屏幕帽的时间。
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.
将表放置在其自己的文件组中。限制文件组的大小。请参阅：
然后添加删除旧日志的作业记录，但这通常比听起来更棘手。使用滑动窗口最有效的方法：如何实现分区表中的自动滑动窗口。如果不可能，那么下一个最好的事情是确保表上的聚集键是日期，以便删除可以有效地删除旧行。
Place the table on its own filegroup. Limit the size of the filegroup. See:
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.
如果您想出于日志记录目的限制表的大小，我不建议考虑通过限制表中存储的记录数来解决问题。相反，对存储日志的表有一个归档或清除进程，此进程可以配置为在达到 X 行数后清除/归档日志，或者稍后您可能希望在 X 分钟后重新配置它/小时/等。如果您关心实际空间，那么最好分析您的日志实际占用了多少空间。了解数据库可用的物理空间后，就可以限制 SQL Server 的数据增长，以确保存储日志信息的数据文件不会超出您的预期。
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.