none
Massive Initial Size Log File RRS feed

  • Question

  • Hi guys, I have a db that with massive initial size transaction logs file amount, it's sucking all the disk.

    What happen if I manually decrease the file by 50%? Just slowing down the performance I believe (if the logs are greater than the Initial Size)...

    Monday, July 8, 2019 10:27 AM

Answers

  • massive initial size

    >  it is taken every hour

    It's massive of it backup every hour? 

    My database backup once a week. At weekend and this take night to be done.

    Shrinking log... hmmm... once had a problem - randomly shown timeout. Do get timed out on growing database/log file. How problematic is yours - need to look on place.

    Optional solution - put additional hard drive for a log file, relocate log and forget about problem. 

    Another solution - run shrink after backup. 


    Sincerely, Highly skilled coding monkey.

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 11:04 AM
  • In the nutshell, log is not truncating (e.g. internal space is not releasing) because some of the processes need the portion of the log. First of all, check log_reuse_wait_desc from sys.databases view. This will tell you what is preventing your log file to truncate.

    Most common ones:

    • LOG_BACKUP - means you need to perform log backup. Make sure to check the value again after you performed it
    • ACTIVE TRANSACTION - you have run-away transaction which has not been committed
    • AVAILABILITY GROUP - something is not right with AlwaysOn AG transport. Most likely one of the secondaries is down
    • REPLICATION - replication agent is not working as expected.

    There are a few more - check documentation.

    After you identified and addressed the root cause, you can shrink the log. Keep in mind two things:

    1. Log growth is expensive operation. SQL Server is zeroing out the new portion of the log file at auto-growth event, blocking all write activity in the database. It is better to do log file management manually preallocating it to the reasonable size. For auto-growth and initial increase, use MB rather than % and increase it in batches of 1000MB each.
    2. You may not be able to shrink log to the small size immediately - it is wrap-around file and sometimes SQL may not shrink it if it is using space close to the end of the log. You can do it a bit later after truncation/wrap-around.

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 12:29 PM
  • Once that the backup is taken the size of the logs should automatically decrease?

    No. Backup will not reduce the size of log file. It only clear the inactive portions from the t.log. To reduce the size of the log you need to shrink it. When you shrink, DO NOT select database or data file, just select the log file.

    To learn more able t.log refer https://www.sqlshack.com/sql-server-transaction-log-architecture/

    Also, I would recommend set the initial size at 25% of the data file size.For other recommendations please see the link below.

    https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-2017#Recommendations


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Edited by SimpleSQL Monday, July 8, 2019 1:32 PM
    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 1:19 PM

  • Once that I take the backup (once per day) the log should auto shrink. Do not understand why the log file is so huge, however, once that the transaction log are backing up every ten minutes I will shrink the log file and it shouldn't grow again.   

    You should *not* plan on shrinking the log file. After you take a log backup, the recoverable portion of the transaction log is marked inactive and any subsequent logging will overwrite that area. Note that this won't release the space back to the OS. So you will continue to see the log being large. 

    As for why the log size is huge, in going through your responses above, it appears that you never took a log backup. There's your answer. The log kept growing as it couldn't truncate and wraparound. Now that you ran your first log backup, almost entire area of the log was relinquished and it's able to write within that area and won't grow beyond that point (of course unless you run a large active transaction that consumes the space currently allocated and grows further). 

    No one can give you a magic number or percentage of log size you should maintain. It depends on your workload. For example, check log utilization for the largest index create/rebuild operation. Pre-allocate it to that size so it doesn't have to auto-grow. Perform regular log backups and that should keep it from growing. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:14 PM
    Monday, July 8, 2019 11:09 PM
  • Thanks everyone. So far I started the transaction log backup (every ten minutes, by maintenance plan) e I did shrink the logs (it took ten seconds, weird). 

    Th situation didn't change, I still have a huge backup

    <https://social.msdn.microsoft.com/Forums/getfile/1459067>



    I don't understand why. I did change the Initial size last night but now it is again at almost 120 GB...

    Are you talking about the transaction-log file itself, or are you talking about the backup-file for the transaction log? You said backup above, but the in screen shot is says "SQL Server Transaction Log file". And since you have hidden the file name, we can't draw any conclusion from the file name.

    If the issue is with the transaction log file, log at log_reuse_desc in sys.databases. Keep in mind that you may have to issue a couple CHECKPOINTS. The log file can only truncate the part that comes after the active portion, so if that portion is at the end file, SHRINKFILE will not trim much.

    If the issue is with the backup file, well that is your collected transaction logs so far, and you need to harbour it somewhere. At least if you want to be able to recover the database at some point...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:14 PM
    Tuesday, July 9, 2019 11:25 AM

All replies

  • If log has no active transaction then it should shrink fast. I advise to take log backup before the shrink.

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 8, 2019 10:42 AM
  • I have the log backup in the maintenance plan, it is taken every hour. It should be enough, what do you think?
    Monday, July 8, 2019 10:43 AM
  • Check the log file usage, if it showing minimum usage then shrink 

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 8, 2019 10:48 AM

  • What happen if I manually decrease the file by 50%? Just slowing down the performance I believe (if the logs are greater than the Initial Size)...

    Nothing much it may grow again if needed and the initial size is misnomer. If you need the space do it

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 8, 2019 10:52 AM
    Moderator
  • massive initial size

    >  it is taken every hour

    It's massive of it backup every hour? 

    My database backup once a week. At weekend and this take night to be done.

    Shrinking log... hmmm... once had a problem - randomly shown timeout. Do get timed out on growing database/log file. How problematic is yours - need to look on place.

    Optional solution - put additional hard drive for a log file, relocate log and forget about problem. 

    Another solution - run shrink after backup. 


    Sincerely, Highly skilled coding monkey.

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 11:04 AM
  • Using 

    DBCC SQLPERF(logspace)

    I can see that the % used by the db is 99%.

    Does it mean is correctly using the logs or is something to do with the initial size?

    Monday, July 8, 2019 12:01 PM
  • In the nutshell, log is not truncating (e.g. internal space is not releasing) because some of the processes need the portion of the log. First of all, check log_reuse_wait_desc from sys.databases view. This will tell you what is preventing your log file to truncate.

    Most common ones:

    • LOG_BACKUP - means you need to perform log backup. Make sure to check the value again after you performed it
    • ACTIVE TRANSACTION - you have run-away transaction which has not been committed
    • AVAILABILITY GROUP - something is not right with AlwaysOn AG transport. Most likely one of the secondaries is down
    • REPLICATION - replication agent is not working as expected.

    There are a few more - check documentation.

    After you identified and addressed the root cause, you can shrink the log. Keep in mind two things:

    1. Log growth is expensive operation. SQL Server is zeroing out the new portion of the log file at auto-growth event, blocking all write activity in the database. It is better to do log file management manually preallocating it to the reasonable size. For auto-growth and initial increase, use MB rather than % and increase it in batches of 1000MB each.
    2. You may not be able to shrink log to the small size immediately - it is wrap-around file and sometimes SQL may not shrink it if it is using space close to the end of the log. You can do it a bit later after truncation/wrap-around.

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 12:29 PM
  • I am seeing LOG_BACKUP and I am afraid to change the value in the Initial Size...
    Monday, July 8, 2019 12:53 PM
  • I am seeing LOG_BACKUP and I am afraid to change the value in the Initial Size...
     

    well.. that means you have never taken a log backup.. then update your maintenance solution to include log backup. 

    Take a look at SQL Server Maintenance Solution 



    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Monday, July 8, 2019 1:03 PM
    Monday, July 8, 2019 12:55 PM
  • You are right, I spotted that the DB wasn't in the list of db whit the Transaction Log backup running every ten minutes (manteinance plan). 

    Once that the backup is taken the size of the logs should automatically decrease?

    Monday, July 8, 2019 1:07 PM
  • I am seeing LOG_BACKUP and I am afraid to change the value in the Initial Size...

    Fist of all, address the issue. Perform log backup and see if log is truncating. If not - check the value again, truncation may be prevented by multiple issues. Obviously, if issue is missed log backups - make sure to implement proper backup strategy.

    If/when log is truncated - monitor the size of used portion of the log for a while to understand how much space  you really need. You can use script below - run it in context of the database and see how much space in the log is used/free. Based on that data, you can make a decision to shrink the log. Keep some additional space pre-allocated though - e.g. if your typical log usage is 5GB, I would not shrink it below 8-10GB.

    select 
    	db_name() as DBName
    	,name as [FileName]
    	,physical_name as [Path]
    	,size / 128.0 as CurrentSizeMB
    	,size / 128.0 - convert(int,fileproperty(name,'SpaceUsed')) / 128.0 as FreeSpaceMb
    from	
    	sys.database_files
    
    Initial size at this point is irrelevant. The database and log file have been created. Current size is what matters.

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Monday, July 8, 2019 1:08 PM
  • You are right, I spotted that the DB wasn't in the list of db whit the Transaction Log backup running every ten minutes (manteinance plan). 

    Once that the backup is taken the size of the logs should automatically decrease?

    Size of the file would not decrease. But amount of free space in the file should increase assuming LOG BACKUP is the only issue. you can use the script from my previous post to check.

    You need to shrink the log to reduce actual file size.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com


    Monday, July 8, 2019 1:09 PM
  • Once that the backup is taken the size of the logs should automatically decrease?

    No. Backup will not reduce the size of log file. It only clear the inactive portions from the t.log. To reduce the size of the log you need to shrink it. When you shrink, DO NOT select database or data file, just select the log file.

    To learn more able t.log refer https://www.sqlshack.com/sql-server-transaction-log-architecture/

    Also, I would recommend set the initial size at 25% of the data file size.For other recommendations please see the link below.

    https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-2017#Recommendations


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Edited by SimpleSQL Monday, July 8, 2019 1:32 PM
    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:13 PM
    Monday, July 8, 2019 1:19 PM
  • Ok, guys. Thanks a lot to everyone, your help is much appreciate. 

    Situation: I put the DB in the manteinance plane and now it started to take the Transaction Log Backup every ten minutes. The backup is growing up:

    14:22:30

    14:22:50

    meanwhile I am writing. 

    Shall I wait that finish and after I can take the backup?

    The backup should be taken every ten minutes but what happens if the first (14:10) is not entirely taken when the second should start (14:20).

    I hope I am clear, DBA is not my main activity..

    Monday, July 8, 2019 1:25 PM
  •  Check log_reuse_wait_desc from sys.databases view. What is it showing now?

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, July 8, 2019 1:31 PM
  • Ok, guys. Thanks a lot to everyone, your help is much appreciate. 

    Situation: I put the DB in the manteinance plane and now it started to take the Transaction Log Backup every ten minutes. The backup is growing up:

    14:22:30

    14:22:50

    meanwhile I am writing. 

    Shall I wait that finish and after I can take the backup?

    The backup should be taken every ten minutes but what happens if the first (14:10) is not entirely taken when the second should start (14:20).

    I hope I am clear, DBA is not my main activity..

    Heh, interesting. During all my years with SQL Server I have never tried nor thought to run parallel t-log backups on the same DB. :) Well, there is no danger to try - it will either work or not. I would suspect the latter.

    I would suggest to run the first backup manually - it will take significantly longer.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Monday, July 8, 2019 1:35 PM
  • It is not parallel, it is just one. I was giving evidence about the size which were growing up (and it is still growing up). Now I am afraid for the final size of the first transaction log... 
    Monday, July 8, 2019 1:38 PM
  • It is not parallel, it is just one. I was giving evidence about the size which were growing up (and it is still growing up). Now I am afraid for the final size of the first transaction log... 

    It would not exceed t-log size. Are you using backup compression? Cancel current backup and enable it (unless you have very high CPU usage on the server).

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Monday, July 8, 2019 1:40 PM
  • Yep, the first backup took 27 GB, the second just 29 kb, the third 28 kb, etc.

    I think I can shrink the log file now

    Monday, July 8, 2019 2:14 PM
  • >Does it mean is correctly using the logs or is something to do with the initial size?

    It mean that log are full with required data.  If you reduce size of the file - it will grow again. Need to look on what force it to grow. 


    Sincerely, Highly skilled coding monkey.

    Monday, July 8, 2019 2:45 PM
  • >Does it mean is correctly using the logs or is something to do with the initial size?

    It mean that log are full with required data.  If you reduce size of the file - it will grow again. Need to look on what force it to grow. 


    Sincerely, Highly skilled coding monkey.

    Once that I take the backup (once per day) the log should auto shrink. Do not understand why the log file is so huge, however, once that the transaction log are backing up every ten minutes I will shrink the log file and it shouldn't grow again.   
    Monday, July 8, 2019 2:48 PM

  • Once that I take the backup (once per day) the log should auto shrink. 
    Unless you have auto shrink turned on for database that is not going to happen and do not set auto shrink to true. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 8, 2019 6:24 PM
    Moderator

  • Once that I take the backup (once per day) the log should auto shrink. Do not understand why the log file is so huge, however, once that the transaction log are backing up every ten minutes I will shrink the log file and it shouldn't grow again.   

    You should *not* plan on shrinking the log file. After you take a log backup, the recoverable portion of the transaction log is marked inactive and any subsequent logging will overwrite that area. Note that this won't release the space back to the OS. So you will continue to see the log being large. 

    As for why the log size is huge, in going through your responses above, it appears that you never took a log backup. There's your answer. The log kept growing as it couldn't truncate and wraparound. Now that you ran your first log backup, almost entire area of the log was relinquished and it's able to write within that area and won't grow beyond that point (of course unless you run a large active transaction that consumes the space currently allocated and grows further). 

    No one can give you a magic number or percentage of log size you should maintain. It depends on your workload. For example, check log utilization for the largest index create/rebuild operation. Pre-allocate it to that size so it doesn't have to auto-grow. Perform regular log backups and that should keep it from growing. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:14 PM
    Monday, July 8, 2019 11:09 PM
  • Thanks everyone. So far I started the transaction log backup (every ten minutes, by maintenance plan) e I did shrink the logs (it took ten seconds, weird). 

    Th situation didn't change, I still have a huge backup

    I don't understand why. I did change the Initial size last night but now it is again at almost 120 GB...

    The backup was taken at 20.37 and since then the logs backup every ten minutes,

    hence why I have the huge backup taken at 21.13?


    • Edited by DIEGOCTN Tuesday, July 9, 2019 8:48 AM
    Tuesday, July 9, 2019 8:47 AM
  • Are you using backup compression ? Backup has only data and few transaction logs so if it is showing huge value then it is data. Also hope you are not appending backups to any backup set. Show me the backup command 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 9, 2019 9:04 AM
    Moderator
  • This is the point. I can see the jobs that triggers the backups and teh transaction logs backup (either in the maintenance plane and in job agent) but I cannot see where the huge file comes from. There is nothing in Agent and nothing in the Maintenance Plan.  
    • Edited by DIEGOCTN Tuesday, July 9, 2019 9:53 AM
    Tuesday, July 9, 2019 9:26 AM
  • Now I am investigating with the third company, I start to suspect that this belong top some disk mirroring. I spot the below in the window logs:

    Volsnap

    The oldest shadow copy of volume C: was deleted to keep disk space usage for shadow copies of volume C; below the user defined limit

    No sure was entirely means but it was ran at the same time of the creation of the huge log file. 

    Tuesday, July 9, 2019 10:13 AM
  • Thanks everyone. So far I started the transaction log backup (every ten minutes, by maintenance plan) e I did shrink the logs (it took ten seconds, weird). 

    Th situation didn't change, I still have a huge backup

    <https://social.msdn.microsoft.com/Forums/getfile/1459067>



    I don't understand why. I did change the Initial size last night but now it is again at almost 120 GB...

    Are you talking about the transaction-log file itself, or are you talking about the backup-file for the transaction log? You said backup above, but the in screen shot is says "SQL Server Transaction Log file". And since you have hidden the file name, we can't draw any conclusion from the file name.

    If the issue is with the transaction log file, log at log_reuse_desc in sys.databases. Keep in mind that you may have to issue a couple CHECKPOINTS. The log file can only truncate the part that comes after the active portion, so if that portion is at the end file, SHRINKFILE will not trim much.

    If the issue is with the backup file, well that is your collected transaction logs so far, and you need to harbour it somewhere. At least if you want to be able to recover the database at some point...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by DIEGOCTN Monday, July 15, 2019 4:14 PM
    Tuesday, July 9, 2019 11:25 AM
  • Hi Erland, thanks. I am talking about the Transaction Log File. 

    Last night I 1) shrinked the log file 2) changed the Initial size to 20000.

    but now everything is back (especially the initial size). 

    Tuesday, July 9, 2019 12:18 PM
  • So why do you care about the initial size?

    If you shrank the file to a smaller size, and the file is now back to the size it had yesterday, I think that we can conclude that the workload requires that size of the log file, and you should not meddle with it any more.

    ...or you should double-check that you performed the shrinking operation on the same file and database as you are looking at now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 9, 2019 3:10 PM

  • The log file can only truncate the part that comes after the active portion, so if that portion is at the end file, SHRINKFILE will not trim much.

    I am assuming by "truncate" in the above context, you meant "trim", because truncate technically can span the inactive VLFs either before or after the active portion of the log, and not necessarily after :)

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, July 9, 2019 4:09 PM
  • Yep, the first backup took 27 GB, the second just 29 kb, the third 28 kb, etc.

    I think I can shrink the log file now

    Yes you can.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, July 9, 2019 4:29 PM
    Moderator
  • Thanks everyone. I had to escalate teh issue to Microsoft but your helping was very useful. 
    Monday, July 15, 2019 4:13 PM