Is it safe to delete the tempdb.mdf file manually?

mlissner picture mlissner · Feb 19, 2013 · Viewed 20k times · Source

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.

Answer

Aaron Bertrand picture Aaron Bertrand · Feb 19, 2013

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.