locked
SQL log files growing unexpectedly. How do we configure DB growth to control DB growth. RRS feed

  • Question

  • SQL log files growing unexpectedly.  How do we configure DB growth to control log file growth.

    Our DB files have no size issue but our transaction log files are growing unexpectedly.

    We are going full backups of the Database files and transaction log files successfully.

    There is no mirroring and DB's are configured as full mode.  We are expecting to mirror in the future.

    note - We are running trn backups every 30 minutes.  Full backups on log files and DB files once daily.  temp file backups weekly.


    dsk



    • Edited by kimdav111 Monday, July 18, 2016 3:06 PM correction
    Monday, July 18, 2016 2:24 PM

Answers

  • You can control log file growth the same way you control data file growth. From SSMS, right click on your database, select properties then select Files. From there you can set/change the autogrowth values for log and data files. See http://sqlmag.com/blog/choosing-default-sizes-your-data-and-log-files for so sound concepts on how to determine the right settings for your environment.

    That said, you do need to figure out why the log file is growing unexpectedly. SQL Server does not add stuff to the log unless there are write operations on the database. That could be initiated by users (e.g. insert/update rows) or admins (e.g. index rebuild). Somebody is doing something that results in entries in the log. It might even be someone loading large amounts of data in full logged mode or perhaps some reindex script gone wild. Just setting a limit to log file growth does not solve the problem and will likely cause other issues. For instance, if the tlog hits the new lower limit you set, all subsequent write operations will fail - generally not a good thing.


    No great genius has ever existed without some touch of madness. - Aristotle

    Monday, July 18, 2016 7:25 PM
  • Hi kimdav111

    First of all you could configure just an alerts in you SQL, something like that:

    USE [msdb]
    GO
    
    /****** Object:  Alert [BeCarefulLogs_DK]    Script Date: 18/07/2016 16:59:21 ******/
    EXEC msdb.dbo.sp_add_alert @name=N'BeCarefulLogs_DK', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@category_name=N'[Uncategorized]', 
    		@performance_condition=N'Databases|Log File(s) Size (KB)|DK_xxxxxxx_PRO|>|24999999', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    
    

    When the size of the log reach 25 Gb you can tell the alert trigger an email to you, for example.

    This way you'll be awared of log files and be able to react asap

    Monday, July 18, 2016 3:01 PM
  • In addition, read this site how to configure log file

    http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 19, 2016 5:52 AM
    Answerer

All replies

  • Hi kimdav111

    First of all you could configure just an alerts in you SQL, something like that:

    USE [msdb]
    GO
    
    /****** Object:  Alert [BeCarefulLogs_DK]    Script Date: 18/07/2016 16:59:21 ******/
    EXEC msdb.dbo.sp_add_alert @name=N'BeCarefulLogs_DK', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@category_name=N'[Uncategorized]', 
    		@performance_condition=N'Databases|Log File(s) Size (KB)|DK_xxxxxxx_PRO|>|24999999', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    
    

    When the size of the log reach 25 Gb you can tell the alert trigger an email to you, for example.

    This way you'll be awared of log files and be able to react asap

    Monday, July 18, 2016 3:01 PM
  • You can control log file growth the same way you control data file growth. From SSMS, right click on your database, select properties then select Files. From there you can set/change the autogrowth values for log and data files. See http://sqlmag.com/blog/choosing-default-sizes-your-data-and-log-files for so sound concepts on how to determine the right settings for your environment.

    That said, you do need to figure out why the log file is growing unexpectedly. SQL Server does not add stuff to the log unless there are write operations on the database. That could be initiated by users (e.g. insert/update rows) or admins (e.g. index rebuild). Somebody is doing something that results in entries in the log. It might even be someone loading large amounts of data in full logged mode or perhaps some reindex script gone wild. Just setting a limit to log file growth does not solve the problem and will likely cause other issues. For instance, if the tlog hits the new lower limit you set, all subsequent write operations will fail - generally not a good thing.


    No great genius has ever existed without some touch of madness. - Aristotle

    Monday, July 18, 2016 7:25 PM
  • Hi,

    Hard to tell without some audit trace, but those log "explosive" behaviors are typically due to large bulk insertions, massive index reorganize/rebuild and table purging (delete from instead of truncate table)

    Monday, July 18, 2016 8:24 PM
  • Agreed. Start by monitoring for a pattern in log file size and used space in log. For instance capture every 5 minutes to a table and see how it behaves over a day or week. Based on that pattern you can then try to hint down what is causing this. My bet is on an Agent job.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, July 18, 2016 8:32 PM
  • There are few important things in SQL Server which needs to maintain regularly and stop log file from growing unexpectedly. Read here:

    http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

    http://www.sqlmvp.org/transaction-log-is-too-big-or-growing-unexpectedly/


    Tuesday, July 19, 2016 5:06 AM
  • In addition, read this site how to configure log file

    http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 19, 2016 5:52 AM
    Answerer
  • Hi, overall REBUILD. In live we increase 1000% log's size after selective REBUILD
    Tuesday, July 19, 2016 7:24 AM
  • Thank You so much for sharing the answer. I was in search of this answer. At last i got answer from this forum. Also i have read  the important things in SQL Server which needs to maintain regularly and stop log file from growing unexpectedly.
    Tuesday, July 19, 2016 7:27 AM