tempdb Transaction Log File

Question

tempdb Transaction Log File

Bhushan Kalla

Default port

Points: 1475
More actions
December 28, 2005 at 8:47 pm

#112121

Hi Everyone
I come accross a very strange situation that transaction log file is full of tempdb database when I joined two tables and trying to insert the result set into some physical table TableA. The result set is as big as 50million records and my disk space is 28Gb available for tempdb log files. I really appreciate if any one suggest me some idea to join the tables and populate those results into some table TableA without filling tempdb transaction log file. (My maximum disk space is 28GB). Moreover I am not able to change the physical location of log file to a bigger disk because of SQL Server 2000 constraints.

Thanks,
Sekhar

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

You'll need to reduce the size of the result set, or remove some things like ordering. What kind of 50M result set can you actually work with? If this is for export, why not bcp it out?

SQLBill SSC Guru Points: 51440 More actions · Answer 2

quote: I am not able to change the physical location of log file to a bigger disk because of SQL Server 2000 constraints. :endquote

What constraints? I moved my log files to bigger disks without any problems.

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736255

December 29, 2005 at 9:07 am

#611938

Maybe no more disks around

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Okay, I'll buy that Steve...but my point is:

That isn't a "SQL Server 2000 constraint" (quote from original poster). That's a hardware constraint.

-SQLBill

Bhushan Kalla Default port Points: 1475 More actions · Answer 5

I am talking about Tempdb database log file not the main database log file. I tried to change the location of the tempdb log file but it is thrown an error that tempdb database log file physical location cannot changed.

Sekhar

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

Jo Pattyn

SSC-Dedicated

Points: 32347

December 29, 2005 at 12:16 pm

#612019

you can also add a second logfile for your tempdb

SQLBill SSC Guru Points: 51440 More actions · Answer 7

How did you try to change it. I have moved my TEMPDB without any problem at all.

Great article for moving system databases:

Moving System Databases - A Checklist by Christoffer Hedgate

http://www.sqlservercentral.com/columnists/chedgate/movingsystemdatabasesachecklist.asp

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 8

One thing that MIGHT be causing that problem...I believe the TEMPDB data file AND log file must be moved together.

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 9

http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp

Vladan SSC-Insane Points: 22072 More actions · Answer 10

Can you divide the insert into smaller chunks? I did similar thing recently, was copying some 40 million rows into another DB (for archiving purposes) and the first attempt failed because of tempdb full. I adjusted the query so that it always copied only around 1 million rows, and had no problem completing the action then. Just find some column that you can use as a parameter - date, identity column or any other where you can be sure that nothing will be skipped or copied twice.