Solved

How and Why does a Transaction Log grow ?

Posted on 2011-02-17
12
520 Views
Last Modified: 2012-05-11
Today the support team were performing month data upload(which was around 600 MB), and the log started growing indefinitely till 6GB(that was all available on the server)and hence tran log full, it has around 400 VLFs when checked, ....here comes my brightest BA who wants to know why is it taking 6GB and waiting to grow beyond. I have tried my best to convince him thats how the log works and its by design kinda things...thats as far as i know. telling him that we can only control the growth and cannot say it use this much only and complete the transaction within that space...

Guys...i wanna know how much log space does a unit of data consumes...say 1 unit is one MB
Here in our case,the DML op is an insert
0
Comment
Question by:anilkullam
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 8

Expert Comment

by:Toxacon
Comment Utility
Basically, everything is written only to log. During full backup, the log should be dumped to the database. Incremental backup will backup only the log as nothing has changed in the DB itself. (Every change is in the Log only.)

You need to change the database recovery model to simple. Then do a full backup and the Transaction Log will be able to shrink.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
hi

you cant traslate the size of the trn log to transaction size since every trn changes in size i.e.
the statement size and length of data.

whet you should do is use BULLKED LOGGED loads.

before you load data to your server change the database recovery parameter to BULK_LOGGED.

this should minimize log size by up to 90%
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
@Toxacon

changing the database recovery model will not minimize the log size.
you need to use bulked_logged

also there is no such thing as a "Incremental backup " in sql server but a "Differencial backup"
and this wouldnt help.
0
 

Author Comment

by:anilkullam
Comment Utility
Hi all, i knew i wud get this kind of replys....apart from controlling the log and how ....
my question is...what is the comparative growth of log in terms of data uploaded activity...
say 1 MB of data is inserted then what is the log growth...and what if data is 1 gb and more
0
 
LVL 8

Expert Comment

by:Toxacon
Comment Utility
@ashilo

The recovery model will dictate the amount of data kept in the Log and therefore it will affect the size. If using Simple Model, the Log will be truncated at every checkpoint and will not be kept in the Log.

Bulk-Logged is a temporary recovery model for big data inserts. You can use that temporarily but Microsoft does not recommend it for continuous use.
0
 

Author Comment

by:anilkullam
Comment Utility
I missed this info for you...the db is in simple recovery model.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 8

Expert Comment

by:Toxacon
Comment Utility
The data is arranged in blocks of 8kB on one page so big amounts of data practically consume as much disk space as they are of size. In addition to that, the indices, if any, will consume disk space.

When speaking of Transaction Log, every change will consume transaction metadata space along with the data itself.
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 250 total points
Comment Utility
@ilkullam:

use this procedure to resulve the data / log ratio.

what i think you should do is create a new database and load a sample of data to it then use this proc to analyze the ratio.

CREATE PROCEDURE [MetaBOT].[usp_LogWatch] AS
DECLARE @SQL VARCHAR(5000)
--Clean up temp objects if not properly done so previously
IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '#usp_LogWatch_Results')
   BEGIN
       DROP TABLE #usp_LogWatch_Results
   END
--Create temporary table to store results
CREATE TABLE #usp_LogWatch_Results ([Database Name] sysname, [File Type] VARCHAR(4), [Total Size in Mb] INT)
--Create SQL script to run against all databases on the instance
SELECT @SQL =
'USE [?]
INSERT INTO #usp_LogWatch_Results([Database Name], [File Type], [Total Size in Mb])
SELECT DB_NAME(), [File Type] =
CASE type
WHEN 0 THEN ''Data'''
+
           'WHEN 1 THEN ''Log'''
+
       'END,
[Total Size in Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END
FROM sys.database_files
'
--Run the command against each database
EXEC sp_MSforeachdb @SQL
SELECT D.[Database Name], D.[Total Data File Size In Mb], L.[Total Log File Size In Mb],
        CAST(CAST(L.[Total Log File Size In Mb] AS decimal(8,1))/CAST(D.[Total Data File Size In Mb]
             AS decimal(8,1)) AS decimal(4,2)) AS [Log::Data Ratio]
FROM
        (
        SELECT [Database Name], [File Type], SUM([Total Size in Mb]) AS [Total Data File Size In Mb]
        FROM #usp_LogWatch_Results
        WHERE [File Type] = 'Data'
        GROUP BY [Database Name], [File Type]
        ) AS D INNER JOIN
        (
        SELECT [Database Name], [File Type], SUM([Total Size in Mb]) AS [Total Log File Size In Mb]
        FROM #usp_LogWatch_Results
        WHERE [File Type] = 'Log'
        GROUP BY [Database Name], [File Type]
        ) AS L ON D.[Database Name] = L.[Database Name]
WHERE L.[Total Log File Size In Mb] > 500 AND
        CAST(CAST(L.[Total Log File Size In Mb] AS decimal(8,1))/CAST(D.[Total Data File Size In Mb]
             AS decimal(8,1)) AS decimal(4,2)) > 0.5
ORDER BY CAST(CAST(L.[Total Log File Size In Mb] AS decimal(8,1))/CAST(D.[Total Data File Size In Mb]
             AS decimal(8,1)) AS decimal(4,2)) DESC,
        L.[Total Log File Size In Mb] DESC
--Clean up your temporary objects
DROP TABLE #usp_LogWatch_Results
0
 
LVL 8

Expert Comment

by:Toxacon
Comment Utility
If it is already in Simple Model, the do an individual file shrink after a Full Backup. You can also use Enterprise Manager or SQL Management Studio to get a visual report on empty space in the Log.
0
 

Author Comment

by:anilkullam
Comment Utility
in generic terms how much space does a log may require to insert one gb of data
0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 250 total points
Comment Utility
if you code to do insert

try make a BEGIN TRANSACTION / COMMIT TRANSACTION block, let say every 1000 or 10000 insert
this might help to control the log size.
0
 

Author Closing Comment

by:anilkullam
Comment Utility
I still didnt get my answer yet guys...in generic terms/ideally how much space does the log require for an insert operation for inserting 1GB of data...

As far as the suggestions concerned cant go with them since the activity is being done by the support team where they sit at a diff place.

Out of ur experience, pls post the values
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now