Hi Dave,
Thanks so much for your help. I'm using linked server, so from the
destination database server DEST my query looks like this:
(forgive me if my SQL coding stinks but I still have so much to learn!)
Here is what I was trying to do that made such a mess of my tempdb (don't
know why!!!):
(This isn't my exact exact query because I can't access that other machine
from where I am right now, but
this is almost exactly it. The query would run but only could succeed if
there was MASSIVE space avail for temp storage,
otherwise I get a lack of space in device for tempdb error when theres 60+
gb free on the drive).
insert into email_subscribers (EmailAddress, OptInDate, Origin)
select email, (select GetDate() as OptInDate), 'WEB SITE
SUBSCRIBE - NOT SPAM' as Origin
from ORG.import.dbo.emailaddrs
where not exists email in (select EmailAddress from
email_subscribers where EmailAddress <> email)
---------- This solution kinda works but as you can guess is a little slow
as I have 30,000,000 records
-- Use join to filter out duplicates
inner join email_subscribers as S on
S.EmailAddress <> email)
BEGIN
insert into email_subscribers (EmailAddress, OptInDate, Origin)
-- Delete doesn't seem to work against the linked server for some reason
so I actually comment that out
-- but my plan was to actually MOVE one record at a time
inner join email_subscribers as S on
S.EmailAddress <> email)
END
> Hi David
> See my response to your other post regarding the transaction log. As
> for tempdb, what are you doing that would even use it? How are you
> inserting your records? Can you post your code?
> David Simard
Quote:> > Hello, I'm having trouble with my tempdb, perhaps someone can help?
> > I have a database with 30 M records on SQL server ORG. I want to insert
> > those values into a table on linked SQL server DEST. I can't even get
> > through 10,000 records - the query fails saying my tempdb is full. The
> > thing is, there is 10 GB free on the drive with the tempdb on it and 70
GB
> > free on the drive where the database files are. What's worse, is that
even
> > though I specified that the tempdb should be able to grow dynamically,
it
> > doesn't seem to be growing at all.
> > Is there a way that I can complete this import? Each row is actually
pretty
> > small, less than 255 bytes. Can I bypass transaction log or something?
If
> > its not the tempdb then I run out of room in the transaction log. It
takes
> > 45 min to 2 hours each time my insert fails.
> > I'm trying as an alternative approach using a curser to move one record
at a
> > time, and also a while loop using select top 1 to grab one record at a
time
> > and move it (insert into DEST then delete from ORG). However, these
still
> > seem to wrap the operation into a transaction, even with set
> > IMPLICIT_TRANSATION OFF.
> > Thanks!
> > Frustrated!
> > david_berman(at)bose.com
by