SQL SERVER – How to Stop Growing Log File Too Big

I was recently engaged in Performance Tuning Engagement in Singapore. The organization had a huge database and had more than a million transactions every hour. During the assignment, I noticed that they were truncating the transactions log. This really alarmed me so I informed them this should not be continued anymore because there’s really no need of truncating or shortening the database log. The reason why they were truncating the database log was that it was growing too big and they wanted to manage its large size. I provided two different solutions for them. Now let’s venture more on these solutions.

If you are jumping over this post to leave a comment, please read first the two options as follows:

1) Convert the Recovery Model to Simple Recovery

If you are truncating the transaction logs, this means you are breaking the T-Log LSN (Log Sequence Numbers). This follows that if disaster comes, you would not be able to restore your T-Logs and there would be no option for you to do point in time recovery. If you are fine with this situation and there is nothing to worry, I suggest that you change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.

2) Start Taking Transaction Log Backup

If your business does not support loss of data or requires having point in time recovery, you cannot afford anything less than Full Recovery Model. In Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits. If you are taking an hourly T-Log backup, your T-Log would grow until one hour but after this the T-Log backup would truncate all the ‘committed’ transactions once you take it. Doing this would lead the size of the T-Log not to go down much, but it would rather be marked as empty for the next hour’s T-Log to populate.

With this method, you can restore your database at Point of Time if a disaster ever happens at your server.

Let us run an example to demonstrate this. In this case, I have done the following steps:

  1. Create Sample Database in FULL RECOVERY Model
  2. Take Full Backup (full backup is must for taking subsequent backup)
  3. Repeat Following Operation
    1. Take Log Backup
    2. Insert Some rows
    3. Check the size of Log File
  4. Clean Up

After a short while, you will notice that the Log file (ldf) will stop increasing but the size of the backup will increase.

If you have an issue with your log file growth, I suggest that you follow either of the above solutions instead of truncating it.

/* FULL Recovery and Log File Growth */
USE [master]
GO
-- Create Database SimpleTran
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')
BEGIN
ALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SimpleTran]
END
GO
CREATE DATABASE [SimpleTran]
GO
-- Set Database backup model to FULL
ALTER DATABASE [SimpleTran] SET RECOVERY FULL
GO
BACKUP DATABASE [SimpleTran] TO DISK = N'D:\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Check Database Log File Size
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Create Table in Database with Transaction
USE SimpleTran
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[RealTempTable]
GO
CREATE TABLE RealTempTable (ID INT)
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- Take Full Backup
BACKUP LOG [SimpleTran] TO DISK = N'D:\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction multiple times and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
/*
Now run following code multiple times.
You will notice that it will not increase the size of .ldf file but will for sure
increasethe size of the log backup.
*/
-- Second Time
-- START
BACKUP LOG [SimpleTran] TO DISK = N'D:\SimpleTran.log' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Run following transaction and check the size of T-Log
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the size of the Database
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SimpleTran'
GO
-- END
--Clean Up
USE MASTER
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')
BEGIN
ALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SimpleTran]
END


If you run the code that is listed between START and END, you would get the following results almost every time:

SQL SERVER - How to Stop Growing Log File Too Big loggrow

This validates our earlier discussion. After seeing this article, the Singapore team implemented Log Backup instead of Log Truncate right away. Let me know what you think about this article.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2008 R2 – PowerPivot for Microsoft Excel 2010 – RTM
Next Post
SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

Related Posts

47 Comments. Leave new

  • Hi Pinal..
    I have some database for which recovery model is set to full.Now for some databases the ldf size has become large.I tried taking log backup regularly,but i didn’t find decrease in physical size of ldf. I followed what stan20 has told in this link
    But it only reduced logical file size. So how i can mange size of ldf file?

    Reply
  • Hello,
    I have sql server 2008 R2 database with filestream data type enabled. When i do a full backup to .bak file does it include all the files located on physicial location for filestream? The reason i ask because the database .bak file size grew larger than 6b in one month where as there is not much data in the database. What can be reason behind large .bak file? What can be done to reduce the size of it.

    Regards
    Manish

    Reply
  • we have full database and log backups scheduled..the log backups are growing now instead of log file…can we shrink or truncate these log backup files (not the log file) ??

    Reply
  • Mahmoud Elkerdawy
    December 3, 2013 1:40 am

    Dear Expertise :
    I have a problem we have a backup file with log file space 150GB I have tried a lot to decrease the log file space even remove it but no hope .

    Reply
    • Mahmoud Elkerdawy
      December 3, 2013 1:41 am

      i have a problem iam working on sql server 2008 r2 mt issue is that we edit our infrastructure 2 times each time we take a full data base backup the problem is our log file initialize with 117 GB space when i try to shrink it come back to its initialized space can you inform me what to do

      Reply
  • SQL 2005
    Hello Pinal,
    I make a full backup from my databases everynight and the size of modellog.ldf is 286 GB.
    How can I reduce the size of the file.

    thank you in Advance
    Alex

    Reply
  • can anyone expain the purpose of log file and data file?

    Reply
  • 1.My log file was full, can i do update_statistics, select and delete statements?
    2.if i used update_statistics, select and delete statements what could be happens?

    Reply
  • I’m seeking your assistance with the SQL backups. They have been running normally until June 1st. On June 1st, the incremental backup increased from an average size of 12GB to 273GB for the largest database. what went wrong. please advise….thanks

    Reply
  • My recovery model is set to simple yet the log file still grows. We don’t use the log file in any way. We make nightly full database backups. What else could be causing the log file to grow?

    Reply
  • Mr Pinal… You are a serious db expert… Your points are practical and solid

    Reply
  • This is just incorrect information. Add a few transactions and then your log file remains as is. That’s probably why Pinal Dev hasn’t replied to questions related to db log file still growing.

    Reply
    • The transaction log file(.ldf) might increase even when Database is in Simple recovery mode. If you have a transaction that takes a long time or does a lot of changes, the log cannot truncate on checkpoint for any of the changes that are still in open transactions or that have started since that transaction started. Basically log file has to retain any and everything that happens in the transaction coz it might have to RollBack. Hence the auto increase (if enabled) of log file size.

      Reply
  • Manoj,

    Just change the database to Full recovery and take Transaction log backup and Also try to shrink log, hope it will reduce the size of the database

    Reply
  • Hi, appreciate the article. Is there open license to use the code mentioned here? (/* FULL Recovery and Log File Growth */)

    Reply
  • Purvesh Prajapati
    August 16, 2018 4:05 pm

    Hi Pinal, I also tried your script as you mentioned but log db size does not decrease.

    Reply
  • Hi ,

    We are facing one big issue with ldf file .I.e., one of our database ldf file is growing rapidly we try to shrink that after taking several log backup but it is not realizing the space and we found log_reuse_desc it showing replication but we haven’t configured any replication on this database even CDC also not enabled .Can you please suggest how to resolve this issue

    Reply
  • we have changed the recovery mode from full to simple even we are unable to shrink ldf file .

    Reply

Leave a Reply

Menu