让 SQL Server 更快地处理数据 - 关闭事务日志? [英] Make SQL Server faster at manipulating data - turn off transaction logging?

问题描述

我使用 SQL Server 2005 作为我进行分析工作的大量数据的数据存储.这不是事务性数据库，因为我没有通过更新或捕获实时数据来访问它.我从我的客户那里获得了一些数据，将它们加载到 SQL Server 中并进行一系列操作.然后我抓取这些数据的一部分并将它们拉入 R，在那里我进行大部分分析.然后我将一些数据推送到 SQL Server 中的表中，并可能进行一两个联接.

I use SQL Server 2005 as a data store for a lot of data I do analytic work on. This is not a transactional database as I am not hitting it with updates or capturing real time data. I get a few gigs of data from my clients, load them into SQL Server and do a series of manipulations. I then grab bits of this data and pull them into R where I do most of my analysis. I then push a little data into tables in SQL Server and maybe do a join or two.

我有一段时间 SQL Server 中的日志越来越大，我认为创建这些日志需要一定程度的开销.如何配置 SQL Server 使其运行时很少或没有日志记录?如果事情变得腐败，我很高兴从头开始.任何想法如何使这一切更快?

I have a heck of a time with the logs in SQL Server getting big and I assume it takes some degree of overhead to create these. How can I configure SQL Server so that it runs with little or no logging? If things get corrupt I am happy to just start from the beginning. Any ideas how to make this all faster?

顺便说一句，无需告诉我如何缩小日志，我已经在这样做了.但我希望我不必首先制作日志.我只使用 DB 来存放数据，因为它太大而无法放入 R 中的内存.

BTW, no need to tell me how to shrink logs, I'm already doing that. But I wish I didn't have to make the logs in the first place. I'm only using the DB to house the data because its too big to fit into memory in R.

我应该使用比 Sql Server 更简单的数据库吗?随意告诉我我正在用大锤杀死一只蚂蚁.但请推荐一个更合适尺寸的锤子.:)

Should I be using a more simple DB than Sql Server? Feel free to tell me I am killing an ant with a sledgehammer. But please recommend a more appropriate sized hammer. :)

推荐答案

如何配置 SQL Server 使其运行时几乎没有日志记录?我

How can I configure SQL Server so that it runs with little or no logging? I

我不相信你可以.

但是，如果您为简单备份配置数据库(服务器上的每个数据库都可以不同)，则日志文件在您备份之前不会增长.这是通过将恢复模式设置为简单"来完成的.

However if you configure the database (each database on a server can be different) for simple backups the log file won't grow until you back it up. This is done by setting the recovery mode to "simple".

对于简单备份，日志仅用于保存事务的状态，直到它们完全写入主数据库.

With simple backups the log is only used to hold the state of transactions until they are fully written into the main database.

这篇关于让 SQL Server 更快地处理数据 - 关闭事务日志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！