如何将数据库A复制到B，然后截断数据库A上的数据，而保留B？
我的 SQL Server 2005 数据库有问题。 数据库必须持续处理每秒 1000 次插入。 当数据库还必须处理数据报告（从而建立索引）时，这被证明是非常困难的。 几天后，速度似乎变慢了，每秒只能实现 300 次插入。 到了 10 天，它几乎无法发挥作用。
要求是存储 14 天的数据。 到目前为止，在一切崩溃之前我只能管理 3 或 4 个。 这个问题有简单的解决办法吗？
我想我可以复制主数据库，让新数据库成为存储 14 天数据库的报告数据库，然后每天截断主数据库。 这行得通吗？
I am having a problem with my SQL Server 2005 database. The database must handle 1000 inserts a sec constantly. This is proving to be very difficult when the database must also handle reporting of the data, thus indexing. It seems to slow down after a couple of days only achieving 300 inserts per sec. By 10 days it is almost non functional.
The requirement is to store 14 days worth of data. So far I can only manage 3 or 4 before everything falls apart. Is there a simple solution to this problem?
I was thinking that I could replicate the primary database allowing the new database to be the reporting database storing the 14 days worth of database, then truncate the primary database daily. Would this work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不太可能希望针对每秒捕获 1000 条记录的数据库运行报告。 我建议使用两个数据库，一个处理持续的插入流，第二个报告数据库仅按一定时间间隔加载记录，方法是查询第一个数据库自上次加载以来的有限集，或者缓存传入数据并单独加载。
然而，要对每天捕获 8600 万行并携带大约 12 亿行的数据库进行近乎实时的报告，将需要大量的规划和硬件需求。 此外，在后端，当您到达第 14 天并开始删除旧数据时，您将为数据库带来更多负载。 如果您可以在没有日志记录的情况下运行，这将对主系统有所帮助，但具有索引需求等的报告系统将需要一些相当重要的性能考虑。
It is unlikely you will want reporting running against a database capturing 1000 records per second. I'd suggest two databases, one handling the constant stream of inserts and a second reporting database that only loads records at an interval, either by querying the first for a finite set since the last load or by caching the incoming data and loading it separately.
However, reporting in near real time against a database capturing 86 million rows per day and carrying approximately 1.2 billion rows will require significant planning and hardware demands. Further, on the backend as you reach day 14 and start to remove old data you will put more load on the database. If you can run without logging that will help the primary system, but the reporting system with indexing demands and such will require some pretty significant performance considerations.
如果服务器有多个硬盘驱动器，我会尝试将数据库（甚至表）拆分为分区。
If the server has multiple harddrives I would try to split the database (or even the tables) in partitions.
是的，您不需要复制数据库，然后动态截断/删除实时数据库。 我的猜测是，速度缓慢是因为您的事务日志疯狂增长？
我认为您是想说您想定期“收缩”数据库。 如果您有完整的备份方案，我认为如果您偶尔备份一次事务日志，这将使事情再次恢复正常。
Yeah, you dont need to copy a database over and then truncate/delete the live database on the fly. My guess is that the slowness is because your transaction logs are growing like crazy?
I think you are trying to say that you want to "shrink" the database periodically. If you have a FULL backup scheme, I think that if you backup the transaction logs once in a while that will shrink things down to normal again.