prasadau2006 (7/31/2012)
Hi All,Is their a way we can manage Transaction log even before it fills up? I have some archive tables, my requirement is to add fields to this tables and default the existing records with certain default values. But unfortunately halway through the update queries it throws an error saying transaction log is full. The reason probably might bcoz of the number of records(millions) and the updates it is doing. I was thinking if i can write my update query in such way that it actually doesnt log each an every thing even after the update for field is done.
Thanks in advance.
I dealt with a similar issue recently. We had a job that inserted a couple billion rows/week and would explode the transaction log.
Doing the update in chunks if essential here.
The script we used looked something like this:
Note: This is functioning template to get you started. It does not take into consideration things like: multiple transaction logs, log-shipping and/or Replication, DB with the Bulk Recovery Model, etc.
First a table for this test:
USE ajbTest; --your database
SET NOCOUNT ON;
-- Create a target table for testing
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'target')
BEGIN
CREATE TABLE [target]
(
IDint,
Datavarchar(100)
PRIMARY KEY (ID)
);
END
GO
And the script:
/******************************************************************************************
Prepare variables
******************************************************************************************/
-- These would be parameters in a stored proc
DECLARE @sampleDataRows INT=555000,
@rowsPerCycle INT=50000,
@logReset INT=1
-- startup message
DECLARE @startupMsgvarchar(1000),
@logsize INT=(SELECT size/128 FROM sys.database_files WHERE type=1),
@maxlogsize INT=(SELECT max_size/128 FROM sys.database_files WHERE type=1),
@transLog varchar(100)=(SELECT physical_name FROM sys.database_files WHERE type=1),
@logName varchar(100)=(SELECT name FROM sys.database_files WHERE type=1),
@dynamicTruncate varchar(200) = 'alter database ' + DB_NAME() + ' set recovery SIMPLE;'
DECLARE @pctLogLeft varchar(6) =
CAST(CAST(CAST(@logsize AS float)/CAST(@maxlogsize AS float)*100 AS decimal(10,2)) AS varchar(5))+'%'
SET @startupMsg='Starting large insert into {Your DB}...'+CHAR(13)+CHAR(13)
SET @startupMsg=@startupMsg+'Current Transaction Log stats for '+DB_NAME()+':'+CHAR(13)
SET @startupMsg=@startupMsg+' Transaction Log File - '+@transLog+CHAR(13)
SET @startupMsg=@startupMsg+' Transaction Log Capacity - '+CAST(@maxlogsize AS varchar(20))+'MB'+CHAR(13)
SET @startupMsg=@startupMsg+' Transaction Log Size - '+CAST(@logsize AS varchar(20))+'MB'
SET @startupMsg=@startupMsg+' ('+CAST(@maxlogsize-@logsize AS varchar(20))
SET @startupMsg=@startupMsg+'MB remaining, '+@pctLogLeft+' of capacity)'+CHAR(13)+CHAR(13)
SET @startupMsg=@startupMsg+'Inserting rows into {your table}...'
PRINT @StartupMsg
/******************************************************************************************
Create sample source and target tables, populate with sample data
******************************************************************************************/
DECLARE @sourceData TABLE
(
IDINT,
Datavarchar(100)
PRIMARY KEY (ID)
);
WITH sampledata (ID,Data) AS
(
SELECT1 [ID],
SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)+
SUBSTRING(CONVERT(varchar(40), NEWID()),0,9) [Data]
UNION ALL
SELECTID+1,
SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)+
SUBSTRING(CONVERT(varchar(40), NEWID()),0,9)
FROM sampledata
WHERE ID < @sampleDataRows
)
INSERT INTO @sourceData
SELECT ID, Data FROM sampledata
OPTION (MAXRECURSION 0);
-- Check to see if there is anything to update
IF NOT EXISTS
(
SELECT a.ID, a.Data FROM @sourceData a
LEFT JOIN [target] b ON a.ID=b.ID
WHERE b.ID IS NULL
)
PRINT CHAR(13)+' ... Nothing to update. Yay! (maybe)'
/******************************************************************************************
-- Begin the insert
******************************************************************************************/
BEGIN
SET ROWCOUNT @rowsPerCycle
WHILE EXISTS
(
SELECT a.ID, a.Data FROM @sourceData a
LEFT JOIN [target] b ON a.ID = b.ID
WHERE b.ID IS NULL
)
BEGIN
-- 1st, check if the translog is > 50% capacity. Shrink the log if True.
-- This will be checked during each iteration
IF @logsize > @maxlogsize/2
BEGIN
PRINTCHAR(13)+'The trans log needs to be shrunk. Shrinking to '+
CAST(@logReset AS varchar(10))+'MB...'
IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'FULL'
BEGIN
EXEC(@dynamicTruncate);
DBCC shrinkfile (@logName,@logReset);
SET @dynamicTruncate = REPLACE(@dynamicTruncate,'SIMPLE','FULL');
EXEC(@dynamicTruncate);
END
ELSE
DBCC shrinkfile (@transLog,@logReset);
END
BEGIN TRAN
INSERT INTO [target]
SELECT a.ID, a.Data FROM @sourceData a
LEFT JOIN [target] b ON a.ID = b.ID
WHERE b.ID IS NULL;
PRINT ' '+CAST(@@ROWCOUNT AS VARCHAR)+' rows inserted.';
COMMIT;
BEGIN
SET @logsize = (SELECT size/128 FROM sys.database_files WHERE type=1)
PRINT ' '+'Log Size: '+CAST(@logsize AS varchar(20))+'MB'
END
END
END
SET ROWCOUNT 0
PRINT CHAR(13)+'All Done.'
How it works:
The variables at the top would be parameters in a stored proc.
@sampleDataRows - the # of rows to use for a test insert
@rowsPerCycle - the # of rows to insert per batch
@logReset - how big to reset the trans log in MB.
The data in the target table is compared to the source table. If records exist in the source database that do not exist in the target the process begins:
First, the size of the transaction log is checked, if it is more than 1/2 of capacity, the trans log is shrunk. If the Recovery Model is FULL then it is first changed to SIMPLE before the shrink, then back to FULL afterwards.
IF (SELECT recovery_model_desc FROM sys.databases WHERE name = DB_NAME()) = 'FULL'
BEGIN
EXEC(@dynamicTruncate);
DBCC shrinkfile (@logName,@logReset);
SET @dynamicTruncate = REPLACE(@dynamicTruncate,'SIMPLE','FULL');
EXEC(@dynamicTruncate);
END
ELSE
DBCC shrinkfile (@transLog,@logReset);
Next the insert begins in chunks (defined by @rowsPerCycle)
BEGIN TRAN
INSERT INTO [target]
SELECT a.ID, a.Data FROM @sourceData a
LEFT JOIN [target] b ON a.ID = b.ID
WHERE b.ID IS NULL;
PRINT ' '+CAST(@@ROWCOUNT AS VARCHAR)+' rows inserted.';
COMMIT;
The log file is checked before each iteration and shrunk as needed.
I included a number of print statements which can be used for logging. For example, If you take this code and run it as a SQL Job, the job log will read like this:
Starting large insert into {Your DB}...
Current Transaction Log stats for ajbTest:
Transaction Log File - E:\SQL_Logs\MSSQL10.MSSQLSERVER\MSSQL\Data\ajbTest_log.ldf
Transaction Log Capacity - 40MB
Transaction Log Size - 36MB (4MB remaining, 90.00% of capacity)
Inserting rows into {your table}...
The trans log needs to be shrunk. Shrinking to 1MB...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
50000 rows inserted.
Log Size: 12MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 23MB
The trans log needs to be shrunk. Shrinking to 1MB...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
50000 rows inserted.
Log Size: 12MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
50000 rows inserted.
Log Size: 19MB
5000 rows inserted.
Log Size: 19MB
All Done.
-- Itzik Ben-Gan 2001