5

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.

I've tried the various solutions that Microsoft provides, and they don't seem to work.

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.

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):

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

3) I've got the Database set to Simple Recovery model.

mlissner
  • 17,359
  • 18
  • 106
  • 169
  • 1
    Show your table structure (including indexes), the general format of your CSV file (a couple of rows is fine), and the number of rows in the file. Also let us know if the table you're trying to bulk insert into has triggers, if you have exotic features like CDC and change tracking, replication, etc. – Aaron Bertrand Feb 19 '13 at 19:20
  • 1
    Surely deleting while offline wont do a great deal as it gets reset at startup? – chrisb Feb 19 '13 at 19:31
  • @chrisb have you tried this? Would you recommend trying it on a production instance? Would you care to volunteer your production instance for this test? – Aaron Bertrand Feb 19 '13 at 19:41
  • @aaron - absolutely not and I would be shot for going near production without N levels of testing first ;) – chrisb Feb 19 '13 at 19:49
  • Note, this is a non-production server...now I'm not sure what advice to follow. – mlissner Feb 19 '13 at 19:54

3 Answers3

4

No, you cannot delete the tempdb mdf file.

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.

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
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.

Kind of a voodoo move, yes, but the file was automatically recreated and no problems occurred.

mlissner
  • 17,359
  • 18
  • 106
  • 169
0

Can you use BCP? That is a recommended way to load large flat files to 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.

Brian O''Byrne
  • 550
  • 2
  • 10
  • MSDN actually [says the opposite](http://msdn.microsoft.com/en-us/library/ms190421(v=sql.105).aspx): "Where possible, use a Transact-SQL statement to bulk import data into SQL Server because Transact-SQL is faster than bcp." – Pondlife Feb 19 '13 at 19:29
  • Fair enough. I'd always understood BCP to be the recommended approach. On topic: there is an MSDN article [link](http://msdn.microsoft.com/en-us/library/ms190422(v=sql.105).aspx)http://msdn.microsoft.com/en-us/library/ms190422(v=sql.105).aspx that seems relevant. – Brian O''Byrne Feb 19 '13 at 19:42