Crazy Tempdb

[boogin]

A Bulk Copy ran this weekend that maxed out the tempdb (by filling the drive).

Details on tempdb.mdf - 37 gig - but only 1.44 MB used.

DBCC shrinkdatabase had no effect - tried calling it from Enterprise manager and Query analyzer.

Theoretically shouldn't I be able to just stop SQL server - delete the tempdb.mdf - and restart? It's supposed to rebuild itself every time the server is started - at least that is my understanding - it would default back to whatever size model DB was. Well, I deleted it - cleared it totally off of the machine (not in the recycle bin - no where). But when you restart the server - it reappears. A 37 gig tempdb.mdf file just shows up. Any ideas?

The drive is maxed and causing the SQL service to behave weirdly. . .

I have done the dreaded reboot after deleting the file and before starting the SQL service - no success - the file still shows back up.

Fun.

 

[Bernadette]

HI There

It sounds like it is actually the "Transaction Log" that has filled up (.ldf) . The same thing happened to me a week ago.

Try the following ..

1. In Query Analyser run the following script

DUMP TRANSACTION WITH TRUNCATE_ONLY

if this doesnt work try

DUMP TRANSACTION WITH NO_LOG

2. You know need to take a fullbackup of your database
You must do this in Query Analyser

DUMP DATABASE <database_name> to <backup_device>


Your transaction log is now completed. You will only loose the uncommited transactions that were in the transaction log.

Hope This Helps
Bernadette

 

[clong]

Hi there,

FYI, the tempdb is used to store temporary procedures, tables, views, etc.

If you are doing any sql development, check over some of your procs. I've found using temporary tables a much easier way to manipulating records but if you perform inserts, selects & updates on the same table in the same proc you can fill it up quite quickly.

I've done this one before
Chris

 

[ScottAW]

FYI - bulk copies do not make entries in the transaction log.

 

[tlbroadbent]

FYI - By default, all row-insert operations performed by bcp are logged in the transaction log. Only if specific conditions are met does the bulk copy do nonlogged operations. For more info, see &quot;Logged and Nonlogged Bulk Copy Operations&quot; in BOL.