2

Server is SQL Server 2014 running in SQL Server 2008 compatability mode. Recovery Model is Full. We have auto shrink turned on (seems this is related to LOP_SHRINK_NOOP)

We have about 4.5 million rows in the log database of which roughly 4 million rows look identical and continue to grow. These rows all have the same information:

Incrementing: Current LSN  
Operation: LOP_SHRINK_NOOP  
Context: LCX_NULL  
Transaction ID: 0000:0000000  
LogBlockGenerated: 0  
TagBits: 0  
Log Record Fixed Length: 24  
Log Record Length: 12316  
Previous LSN: 00000:00000000:00000  
Flag Bits:0   
Every other column past this is NULL  

Does anyone know what causes this?

  • 10
    We have auto shrink turned on <--- WHY? – Aaron Bertrand Jun 6 '16 at 19:33
  • 8
    Do yourself a favour Auto-shrink – turn it OFF! – Kin Shah Jun 6 '16 at 19:36
  • 5
    Unanswered duplicate/similar question here dba.stackexchange.com/q/91035/3690 – Martin Smith Jun 6 '16 at 20:00
  • while we can't find a direct link between auto shrink and these records, our scouring of the internet leads us to believe they are intimately tied. we've turned off auto shrink (this is not ordinarily turned on and we're reviewing how that might have been enabled) – badMonkey Jun 7 '16 at 13:10
  • Auto-shrink causes these records because auto-shrink is a special case of shrink. Shrink is what actually causes them. – usr Jun 7 '16 at 13:32
0

As has been mentioned in the comments auto shrink is a generally bad idea for a number of reasons (see the links they've provided) and this is most likely the cause of your transaction log issues

Judging by what you've posted you may have ran something similar to this, but its a useful bit of code which will list out all of the databases in an instance along with the recovery model and what currently is preventing the transaction log from shrinking. the reasons are usually self explanatory, like checkpoint, Log_backup

Using this you should be able to narrow down what is preventing your transaction log from shrinking and hereby having all other transactions making it grow

SELECT instance_name
        , recovery_model_desc [Mode]
        , convert(decimal(12,3),[Data File(s) Size (KB)] * 1.0 / 1024 ) [Data File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Size (KB)] * 1.0 / 1024 ) [Log File(s) Size (MB)]
        , convert(decimal(12,3),[Log File(s) Used Size (KB)] * 1.0 / 1024 ) [Log File(s) Used Size (MB)]
        , [Percent Log Used]
        , log_reuse_wait_desc
    FROM (   
        SELECT os.counter_name
            , os.instance_name
            , os.cntr_value
            ,db.recovery_model_desc
            , db.log_reuse_wait_desc
        FROM sys.dm_os_performance_counters os
        JOIN sys.databases db 
            ON os.instance_name = db.name
        WHERE os.counter_name IN 
        ( 
            'Data File(s) Size (KB)' 
            , 'Log File(s) Size (KB)'
            , 'Log File(s) Used Size (KB)'
            , 'Percent Log Used' 
        ) 
        --AND os.instance_name = 'Titan'  
    ) as SourceTable
    PIVOT (
        MAX(cntr_value) FOR counter_name IN
        ([Data File(s) Size (KB)] 
        , [Log File(s) Size (KB)]
        , [Log File(s) Used Size (KB)]
        , [Percent Log Used])) 
        as PivotTable
    order by case WHEN log_reuse_wait_desc = 'NOTHING' THEN 0 ELSE 1 END desc
        , [Percent Log Used] desc

Personally I have this in a proc in my adminDB that can be ran whenever we see any issues relating to the transaction log to find out the exact state of it and what is the main cause of those issues

Its worth noting additionally for the auto shrink on the tlog, but mainly on the inevitable growth that you will have that the DB will be locked out while it grows the file and re-writes over that area of disk space

| improve this answer | |
  • I am experiencing the same entry in my transaction log and do not have auto-shrink enabled. – Asher Feb 11 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.