Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm getting the following error message

The log file for database 'scratchdb' is full. Back up the transaction log for the database to free up some log space.

when I'm trying to insert new records into my temp table (there is ~1.3 mil rows) to check that it does not exist.

INSERT INTO SCRATCHDB..members_temp
SELECT M.*
FROM SCRATCHDB..members_temp D (NOLOCK) RIGHT OUTER JOIN
(SELECT * FROM PROD..Member (NOLOCK) 
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()) AS M
ON M.MemberNo <> D.MemberNo

Is there a better/efficient way to do this?

share|improve this question
1  
I think you should consider to "Back up the transaction log for the database to free up some log space." Or you could change the recovery model for your db to Simple –  Mikael Eriksson Nov 23 '12 at 6:46
add comment

migrated from stackoverflow.com Nov 23 '12 at 11:06

This question came from our site for professional and enthusiast programmers.

3 Answers

Did you try to run this select? I guess it outputs a LOT of records. For example if M table contains 1000 records it outputs 1000 * 1.3M = 1.3 BILLION records (excepts a few with M.MemberNo=D.MemberNo). So in this case increasing a LOG file has no sense. Try to run this query:

INSERT INTO SCRATCHDB..members_temp
SELECT * FROM PROD..Member (NOLOCK) 
WHERE OutletId IN ('87')
AND DATECREATED >= '06 Nov 2012'
AND DATECREATED <= GETDATE()
AND MemberNo NOT IN (select MemberNo from SCRATCHDB..members_temp (NOLOCK));
share|improve this answer
add comment

You seem to be trying to get (and insert) rows from the subset of Member that do not exist in members_temp. Your approach is incorrect. This is how it works. Suppose members_temp contains rows:

MemberNo  ...
--------  -------
1         ...
2         ...
3         ...
4         ...

and the subset of Member is this:

MemberNo  ...
--------  -------
3         ...
4         ...
5         ...

Then your right outer join on M.MemberNo <> D.MemberNo would produce the following row set:

  (members_temp)            (Member)

MemberNo  ...          MemberNo  ...
--------  -------      --------  -------
1         ...          3         ...
2         ...          3         ...
4         ...          3         ...
1         ...          4         ...
2         ...          4         ...
3         ...          4         ...
1         ...          5         ...
2         ...          5         ...
3         ...          5         ...
4         ...          5         ...

You can see that every row in the joined row set matches the condition, and yet the result doesn't seem to be what you are after, does it. Rows returned from Members contain data already present in members_temp, and they are themselves duplicated as well. As a result you would be inserting duplicate rows in your temporary table.

Instead, you should be doing like this:

INSERT INTO SCRATCHDB..members_temp
SELECT M.*
FROM SCRATCHDB..members_temp D (NOLOCK)
RIGHT OUTER JOIN (
  SELECT *
  FROM PROD..Member (NOLOCK) 
  WHERE OutletId IN ('87')
    AND DATECREATED >= '06 Nov 2012'
    AND DATECREATED <= GETDATE()
) AS M
  ON M.MemberNo = D.MemberNo
WHERE D.MemberNo IS NULL

For the same data samples above, the join in this statement would give you the following rows:

  (members_temp)            (Member)

MemberNo  ...          MemberNo  ...
--------  -------      --------  -------
3         ...          3         ...
4         ...          4         ...
NULL      NULL         5         ...

And the WHERE clause would filter them down to just this:

  (members_temp)            (Member)

MemberNo  ...          MemberNo  ...
--------  -------      --------  -------
NULL      NULL         5         ...

So, the member 5 would be inserted into the temporary table, which, I suppose, is the way you wanted your statement to work.

An alternative to an outer join would be to use NOT IN, like in @valex's answer, or NOT EXISTS like this:

INSERT INTO SCRATCHDB..members_temp
SELECT * FROM PROD..Member M (NOLOCK) 
WHERE OutletId IN ('87')
  AND DATECREATED >= '06 Nov 2012'
  AND DATECREATED <= GETDATE()
  AND NOT EXISTS (
    SELECT *
    FROM SCRATCHDB..members_temp (NOLOCK)
    WHERE MemberNo = M.MemberNo
  )
;

All three methods implement what is technically called an anti-join.

share|improve this answer
add comment

For Microsoft SQL 2005/2008

On a Microsoft SQL 2005/2008 server:

Note: Log in to the Microsoft SQL 2005/2008 Server as an administrator. Open up SQL Management Studio.

Right-click the database that Virtual Center is using.

Click Properties.
Click the Options link.
Set the Recovery Model to Simple.

Click OK.
Once this is complete, right click on the database again.
Click Tasks>Shrink>Files.
On the Shrink Database window select the file type as 'Log' .
The file name appears in the filename drop down as databasename_log.

The space used versus the space allocated displays. After you set the recovery model to Simple, the majority of the space in the transaction log released.

Ensure that the Release unused space radio button is selected.
Click OK on this window to shrink the transaction log.

Space is now freed on the server.

Note: If there is more than one transaction log for the database, perform the above steps for each of the logs.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.