Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

How and Why does a Transaction Log grow ?

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
anilkullam
Asked:
anilkullam
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
ToxaconCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
@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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
anilkullamAuthor Commented:
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
 
ToxaconCommented:
@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
 
anilkullamAuthor Commented:
I missed this info for you...the db is in simple recovery model.
0
 
ToxaconCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
@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
 
ToxaconCommented:
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
 
anilkullamAuthor Commented:
in generic terms how much space does a log may require to insert one gb of data
0
 
JoeNuvoCommented:
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
 
anilkullamAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now