locked
SQL Log files size does not decrease RRS feed

  • Question

  • Hello,

    I have a database where the log size is not decreasing...

    at night I am running

    but after the successful run of this job the size of the log file is still 60 GB+ 

    What could block this file to decrease its size...?

    I tried to run a Backup log same result the log did not changed even with "truncate"

    I tried to shrink it it gave me only 1,5 Gb free...

    Any idea?

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    Thursday, May 21, 2020 9:59 PM

Answers

  • The log will not shrink or change size after a log backup, that's how SQL Server works. If the log is that size then generally it'll need to be that size. If you do want to give space back to the OS, you'll need to move the active VLF from the end of the log file and have the one at the beginning to be active. There are various articles on this and I'll leave it as an exercise for you to achieve this. Once this is done you can shrink the log to the lowest active VLF in the physical file which at this point will be at or near the beginning, giving the space back to the OS.

    Be warned that the log will most likely grow again, growing and shrinking the log in cycles will only lead to performance issues, instead you should leave it at the maximum size that it hits as that's what it seems to need.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Felyjos Friday, May 22, 2020 2:55 AM
    Friday, May 22, 2020 2:39 AM
    Answerer

All replies

  • Looks like there are some sleeping sessions which is holding the lock on some portion of log file. Hence it is not making it free. If you can close all Sleeping sessions, it will free up lock and you will be able to Shrink the file.  
    Thursday, May 21, 2020 11:22 PM
  • Hello,

    Thanks let me check for the Sleeping Sessions.

    But this will just clear a small amount of space...

    Anyway to have the logs committed and release space also...

    Yes I have a bunch of sleeping sessions on this database...(13)

    SELECT DB_NAME(dbid) as 'Database Name', 
    COUNT(dbid) as 'Total Connections' 
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid > 0
    GROUP BY dbid
    SELECT @@MAX_CONNECTIONS AS 'Max Allowed Connections'

    Database Name    Total Connections
    master                                30
    msdb                                    3
    ReportServer                         2
    AmcomAmcPremiseCore    19
    AmcomCore                          3
    AmcomMeta                        13

    spid ecid status loginame hostname blk dbname cmd request_id
    59 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    60 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0
    63 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0
    64 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    68 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0
    69 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    71 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    75 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0
    76 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    80 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    81 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0
    87 0 sleeping                       amcom SRPSPOKMWEB1                                                                                                                     0 AmcomAmcPremiseCore AWAITING COMMAND 0
    94 0 sleeping                       amcom VOPSPOKMOBILE1                                                                                                                   0 AmcomAmcPremiseCore AWAITING COMMAND 0

    I was able to kill the sessions but after 2 minutes they came back and the process is not over!!!

    SELECT DB_NAME(dbid) as 'Database Name', 
    COUNT(dbid) as 'Total Connections' 
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid > 0
    GROUP BY dbid
    SELECT @@MAX_CONNECTIONS AS 'Max Allowed Connections'


    and

    USE master
    go
    
    DECLARE @dbname sysname
    
    SET @dbname = 'Events'
    
    DECLARE @spid int
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
    WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    END

    For information

    Should I pass the database in a Single User mode? before killing the session to prevent the come back of the session too fast?

    USE master;
    GO
    ALTER DATABASE AmcomAmcPremiseCore
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO

    I would like to be sure I could commit the log... through a backup or does another process exists to commit the logs? 

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL







    • Edited by Felyjos Friday, May 22, 2020 1:37 AM
    Friday, May 22, 2020 12:09 AM
  • The log will not shrink or change size after a log backup, that's how SQL Server works. If the log is that size then generally it'll need to be that size. If you do want to give space back to the OS, you'll need to move the active VLF from the end of the log file and have the one at the beginning to be active. There are various articles on this and I'll leave it as an exercise for you to achieve this. Once this is done you can shrink the log to the lowest active VLF in the physical file which at this point will be at or near the beginning, giving the space back to the OS.

    Be warned that the log will most likely grow again, growing and shrinking the log in cycles will only lead to performance issues, instead you should leave it at the maximum size that it hits as that's what it seems to need.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Felyjos Friday, May 22, 2020 2:55 AM
    Friday, May 22, 2020 2:39 AM
    Answerer
  • Thanks Sean, so I will contact the vendor to see if it is normal. It has never been so big for the last three years...we are using the product ... 

    Just surprised that in less than a month the logs size double so the drives were full with no warning !!! and with this growth we don't know what is its maximum size anymore!!!

    Also I thought the logs were also containing transactions which when committed were going in the database itself so this is also wrong... otherwise the logs will loose some records from this step as well...

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Friday, May 22, 2020 2:53 AM
    Friday, May 22, 2020 2:51 AM