SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Populate tables without increasing log
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NifflerX
Starting Member

29 Posts

Posted - 01/07/2014 :  11:10:55  Show Profile  Reply with Quote
Hello,

I've got a data mart that is refreshed daily from my OMS system. It's a fairly large load, about 25,000,000 records, and increases daily (although only by the hundreds). To do the refresh I use a TRUNCATE on the data mart table and then an INSERT INTO from the source database (which is on a different server). This works fine and usually loads in about 20 minutes, which is also fine, but the log file balloons to 7 GB.

Both SQL systems are running SQL 2008 and my data mart is in Simple recovery mode. I don't care about the transaction logs on the data mart, since the data is refreshed daily anyway. My question is, is there any way to accomplish this refresh without my log file getting so large? Thanks so much.

-NifflerX

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/07/2014 :  11:39:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
You would have to do the INSERT in batches. Right now it sounds like it's a single transaction, and that can't clear from the log until the transaction completes.

There is no way to turn off logging.

How is 7GB a problem though? 7GB is tiny.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

NifflerX
Starting Member

29 Posts

Posted - 01/07/2014 :  12:06:59  Show Profile  Reply with Quote
It's not a huge problem. 7 GB isn't a huge deal, but it's not nothing and since it's for a transaction log that doesn't actually have any use I was hoping to get rid of it.

The way I've got it coded is to have each table refreshed by it's own stored procedure. So each procedure truncates a table then refreshes that specific table. I then have another stored procedure that calls each refresh stored procedure in turn. That way it's easy to add or remove table refreshes if new ones are needed or old ones are no longer needed.

Thanks.

-NifflerX
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/07/2014 :  12:15:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
The transaction log does have a use. It is to keep the database consistent.

The only way around this is to break the INSERT into batches. We do deletes in 1000-5000 row batches and keep looping until done. This controls the logging.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

NifflerX
Starting Member

29 Posts

Posted - 01/07/2014 :  12:32:15  Show Profile  Reply with Quote
Sorry, I didn't mean to imply the log didn't have it's use, it's just from my stand point it wasn't that useful since I don't really care about the data in the table since it's refreshed so often.

The breaking into batches makes sense, but at least for the time being I think it's more trouble than it's worth. If you know of any links that have examples of what you're talking about I'd love to look at them, but I think I'll just deal with the log file unless it gets to much bigger. Thanks so much.

-NifflerX
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 01/07/2014 :  12:43:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
I have an old blog that shows one way: http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

Here's another example but with DELETE TOP:

CREATE PROC dbo.isp_Table1_Purge
(@purgeDate datetime, @batch int, @purgeSuccess int OUTPUT, @totalRowsPurged int OUTPUT)
AS

SET NOCOUNT ON

DECLARE @error int, @rc int

SELECT @purgeSuccess = 1, @totalRowsPurged = 0, @rc = 1
 
WHILE @rc <> 0
BEGIN
	BEGIN TRAN

	DELETE TOP (@batch) 
	FROM Table1 
	WHERE CreateDate < @purgeDate

	SELECT @rc = @@ROWCOUNT, @totalRowsPurged = @totalRowsPurged + @rc, @error = @@ERROR

	IF @error <> 0
		GOTO EXIT_PROC

	COMMIT TRAN
END

RETURN

EXIT_PROC:

ROLLBACK TRAN
SET @purgeSuccess = 0
 
RETURN



These were all written for 2000 and 2005. With the newer versions, I use DELETE TOP but also with TRY/CATCH logic. In the CATCH, I'm looking for error 1205 and retrying if it encounters that. 1205 is the deadlock error code. We retry up to 10 times before giving up.


ALTER PROCEDURE [dbo].Blah 
AS
BEGIN

   SET NOCOUNT ON;

   DECLARE @err INT, @retries INT, @rowcnt INT, @cnt INT, @total_rows INT, @note VARCHAR(50), 
      @errorMessage NVARCHAR(4000), @errorSeverity INT, @errorState INT;

   SELECT @total_rows = 0, @rowcnt = 0, @retries = 0

   WHILE (1 = 1) 
     BEGIN 
       BEGIN TRY
         BEGIN TRAN;

         DELETE ...

	     COMMIT TRAN;
	     BREAK;
	   END TRY

	   BEGIN CATCH
         IF (@@TRANCOUNT > 0)
           ROLLBACK TRAN;
                          
         -- 1205 is deadlock error
         IF (ERROR_NUMBER() = 1205 AND @retries < 10) 
            BEGIN
              SET @retries = @retries + 1;
              WAITFOR DELAY '00:00:10';
            END
         -- some other error or done retrying
         ELSE 
            BEGIN
               SELECT @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE();
               INSERT INTO ...;
               RAISERROR (@errorMessage, @errorSeverity, @errorState);
               RETURN;
            END
       END CATCH
   END


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000