none
Database log file growth control for Simple and Full recovery Model RRS feed

  • Question

  • Hi

    I have many databses on single instance (having recovery model simple and full) SQl Server 2012. Database are almost not used by application.

    They are used for Application search/refrence only. My few database logfile size are 5 GB to 120 GB. I am thinking to shrink it. But it might grow again? is there any best way to control this growth without changing application logic etc.

    Regards,

    Deepak



    Thursday, December 12, 2019 6:52 AM

All replies

  • >>>>But it might grow again? is there any best way to control this growth without changing application logic etc.

    If your db is set to FULL recovery mode, so please start perform BACKUP LOG file it will reuse Virtual Log file and keep  you size under control. Otherwise if your db is  set to SIMPLE  recovery mode and see that the log is growing, then investigate long running transactions , huge DML operations that may cause the log to grow , and yes if it grew to the huge size just shrink it

    use dbname

    go

    DBCC SHRINKFILE(2,100)


    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

    Thursday, December 12, 2019 7:59 AM
    Answerer
  • for simple recovery also  if we configure diffrential backup than it will not control log growth?

    Or is there any way to control it.

    Thursday, December 12, 2019 8:21 AM
  • Under the full recovery model, using differential backups can reduce the number of log backups that you have to restore

    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

    Thursday, December 12, 2019 8:34 AM
    Answerer
  • Thursday, December 12, 2019 8:35 AM
    Answerer
  • My Worry area is disk size. I need to control disk growth without changing recovery model.

    I don't need to worry about database restore as there is now new data growth in these database. I can restore database from one month old data also.

    Thursday, December 12, 2019 8:46 AM
  • You can set up alert on disk space and/or on log file growth and issue the SHRINK

    Log file growth alert

     change alert Type to "SQL Server performance condition alert"

    - select Object "SQLServer:Databases"

    - select Counter "Log File(s) Size (KB)"

    - specify database name under Instance

    - set value in KB (rises above NNN KB)

    ------------------------------------------------------------------

    CREATE PROCEDURE [dbo].[sp_DiskAlert]
    AS
    TRUNCATE TABLE Drivetable
    INSERT into Drivetable Exec xp_fixeddrives 
    IF EXISTS (
    SELECT * FROM
    (
    SELECT Drive,[MB Free]/1024 AS GB FROM DriveTable) AS Der
    WHERE Drive='D' AND GB<=30
    )
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'Administrator', 
         @recipients = 'ff@gg.com', 
         @subject = 'Myserver LOW DISK Space on D drive'
    END 
    IF EXISTS (
    SELECT * FROM
    (
    SELECT Drive,[MB Free]/1024 AS GB FROM DriveTable) AS Der
    WHERE Drive='L' AND GB<=8
    )
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'Administrator', 
         @recipients = 'fff@ff.com', 
         @subject = 'Myserver LOW DISK Space on L drive'
    END 


    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


    Thursday, December 12, 2019 8:51 AM
    Answerer
  • I don't need to worry about database restore as there is now new data growth in these database. I can restore database from one month old data also.

    Well, in that case, the transaction log should not grow, unless you are making updates to existing rows.


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

    Thursday, December 12, 2019 10:07 PM
  • How about database internal job and index/statistics update??
    Friday, December 13, 2019 1:46 AM
  • How about database internal job and index/statistics update??

     if your Db's have mostly read only, your log growth should be minimal. Index rebuilds will cause log growth but it again matters, if something is updating( writing) to the database. statistics do not big impact on the Transaction log.

    if you have commands like shrink file\DB, that can cause log growth as well.  if what you mentioned above is true, you should see very minimal log growth but 120 GB seems high.

    The best way to check is ( assuming the DB is in full recovery mode), take log backups every 10 mins and check the log backup file size. notice whats the average log growth for each backup and when it peaks and coorelate that with DB activity ( may be some jobs or application workload) etc to figure why\when your logs are growing. you can also set up Xevents session to track log growth.


    Hope it Helps!!

    Friday, December 13, 2019 5:29 AM
  • for simple recovery also  if we configure diffrential backup than it will not control log growth?

    Or is there any way to control it.

    The plain answer is NO differential backup has no affect.

    My Worry area is disk size. I need to control disk growth without changing recovery model.

    I don't need to worry about database restore as there is now new data growth in these database. I can restore database from one month old data also.

    The point here is if you do not need point in time recovery set recovery model simple and you do not have to worry about log file backup. The checkpoint will truncate logs. 

    If you want to keep it int full recovery you have to take frequent log backups to control the log file. 

    This is basically what you have to do their is not other option. Make sure auto growth is NOT in percentage but in MB


    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

    Friday, December 13, 2019 6:06 AM
    Moderator
  • Hi DeepakTyagi28,

     

    I suggest that you modify the file growth mode in the form of MB growth. If your database is in full recovery mode, make regular log backups, and if you really don't care about log backups, you can set a  job to periodically delete log backups

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 8:33 AM
  • How about database internal job and index/statistics update??

    There are no such internal jobs. Of course, you may have set up index maintenance (which you don't need if does not change) with the not-so-smart built-in maintenance plans. In that case, the log will grow, but then you were also not telling us the correct story that there are no updates.

    Shanky makes an excellent point. If your data does not change a lot, and you are content with restoring a back that is a months old, you should have the database in simple recovery.


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

    Friday, December 13, 2019 12:29 PM