Geek asks and answers

Is it safe to delete tempdb.mdf file manually?

In SQL Server 2008, I am trying to BULK INSERT CSV about 1 GB in size. Since I am doing this, it creates a huge tempdb.mdf file. Right now, this is 35 GB for this 1GB CSV file.

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

I think the easiest way to “compress” the tempdb.mdf file on a non-production system is to simply delete it while the SQL service is down.

Will this cause problems? If so, what problems can be expected?

edits

1) Here is a line from CSV (has about 4M lines):

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 the database describes the information about the table (nothing exotic and triggers): https://gist.github.com/mlissner/4cd13db5a1bbae91dd50

3) I have a database configured for a simple recovery model.

+5
source share
3 answers

In the end, yes, it was safe for me to delete this file. SHRINKFILE did not work sequentially (I don’t know why, maybe something basic), and deleting the file works fine.

The kind of voodoo movement, yes, but the file was automatically recreated, and no problems arose.

+1
source

No, you cannot delete the mdf tempdb file.

If you need to compress the file again, restart SQL Server and then run DBCC SHRINKFILE(). This is a supported operation, unlike any of this “delete mdf file until SQL Server looks” voodoo.

, , tempdb , , .

+4

Can you use BCP? This is the recommended way to upload large flat files to SQL Server.

Alternatively, can you upgrade from a full recovery model to a simple recovery model? This will reduce the number of transactions performed by the server.

0
source

More articles:


All Articles