In SQL Server 2008, I am trying to BULK INSERT a CSV that's about 1GB in size. As I'm doing so, it's creating a huge tempdb.mdf file. Right now, it's 35GB for this 1GB CSV file.
在SQL Server 2008中,我试图BULK INSERT一个大小约为1GB的CSV。正如我所做的那样,它正在创建一个巨大的tempdb.mdf文件。现在,这个1GB CSV文件的容量为35GB。
I've tried the various solutions that Microsoft provides, and they don't seem to work.
我已经尝试过Microsoft提供的各种解决方案,但它们似乎不起作用。
I'm thinking the easiest way to "shrink" the tempdb.mdf file on a non-production system is to just delete it while the SQL service is down.
我认为在非生产系统上“缩小”tempdb.mdf文件的最简单方法是在SQL服务关闭时删除它。
Is that going to cause any problems? If so, what kinds of problems can be expected?
这会导致任何问题吗?如果是这样,可以预期会出现什么样的问题?
Edits
1) Here's a row from the CSV (it has about 4M rows):
1)这是CSV中的一行(它有大约4M行):
PS D:\> gc .\map.items.csv | select -last 1
40747646;jdbc:patent/8046822;8683;other/patent;12/31/69 16:00:00.00 PST;E6 E6 80 6D FD 6D 0B 5F 44 66 4E 1C 35 DE 46 BB 19 36 3C 31 37 67 4D 1D DF 58 A5 99 A8 A0 3B 52;crawled;full_patent_db2;Electronic apparatus, function selection method of electronic apparatus and management system of electronic apparatus;Sony Corporation;Tokyo;03;G06F21/00
2) Here's the DB describe table info (nothing exotic, and no triggers): https://gist.github.com/mlissner/4cd13db5a1bbae91dd50
2)这是DB描述表信息(没有异国情调,没有触发器):https://gist.github.com/mlissner/4cd13db5a1bbae91dd50
3) I've got the Database set to Simple Recovery model.
3)我已将数据库设置为简单恢复模型。
3 个解决方案
#1
4
No, you cannot delete the tempdb mdf file.
不,您无法删除tempdb mdf文件。
If you need to shrink the file again, restart SQL Server, and then run DBCC SHRINKFILE(). This is a supported operation, unlike any of this "delete an mdf file while SQL Server is not looking" voodoo.
如果需要再次收缩文件,请重新启动SQL Server,然后运行DBCC SHRINKFILE()。这是一个受支持的操作,不像任何“删除一个mdf文件,而SQL Server不看”伏都教。
You might be able to optimize your bulk operation so tempdb isn't so heavily hit in the firts place, but it's impossible to tell with the details given what the actual problem is.
您可能能够优化批量操作,因此tempdb在第一个地方不会受到太大影响,但是根据实际问题的具体情况,我们无法确定细节。
#2
1
In the end, yes, it was safe for me to delete this file. SHRINKFILE wasn't working consistently (don't know why, probably something basic), and deleting the file worked perfectly.
最后,是的,删除此文件对我来说是安全的。 SHRINKFILE没有一致地工作(不知道为什么,可能是基本的东西),删除文件工作得很好。
Kind of a voodoo move, yes, but the file was automatically recreated and no problems occurred.
有点巫毒动作,是的,但文件是自动重新创建的,没有出现任何问题。
#3
0
Can you use BCP? That is a recommended way to load large flat files to SQL Server.
你能用BCP吗?这是将大型平面文件加载到SQL Server的推荐方法。
Alternatively can you switch from the full recovery model to the simple recovery model? That will reduce the amount of transaction logging performed by the server.
或者,您可以从完整恢复模型切换到简单恢复模型吗?这将减少服务器执行的事务日志记录量。
#1
4
No, you cannot delete the tempdb mdf file.
不,您无法删除tempdb mdf文件。
If you need to shrink the file again, restart SQL Server, and then run DBCC SHRINKFILE(). This is a supported operation, unlike any of this "delete an mdf file while SQL Server is not looking" voodoo.
如果需要再次收缩文件,请重新启动SQL Server,然后运行DBCC SHRINKFILE()。这是一个受支持的操作,不像任何“删除一个mdf文件,而SQL Server不看”伏都教。
You might be able to optimize your bulk operation so tempdb isn't so heavily hit in the firts place, but it's impossible to tell with the details given what the actual problem is.
您可能能够优化批量操作,因此tempdb在第一个地方不会受到太大影响,但是根据实际问题的具体情况,我们无法确定细节。
#2
1
In the end, yes, it was safe for me to delete this file. SHRINKFILE wasn't working consistently (don't know why, probably something basic), and deleting the file worked perfectly.
最后,是的,删除此文件对我来说是安全的。 SHRINKFILE没有一致地工作(不知道为什么,可能是基本的东西),删除文件工作得很好。
Kind of a voodoo move, yes, but the file was automatically recreated and no problems occurred.
有点巫毒动作,是的,但文件是自动重新创建的,没有出现任何问题。
#3
0
Can you use BCP? That is a recommended way to load large flat files to SQL Server.
你能用BCP吗?这是将大型平面文件加载到SQL Server的推荐方法。
Alternatively can you switch from the full recovery model to the simple recovery model? That will reduce the amount of transaction logging performed by the server.
或者,您可以从完整恢复模型切换到简单恢复模型吗?这将减少服务器执行的事务日志记录量。