2

I just tried shrinking an overly big log file (FULL recovery model). This operation is not expected to always succeed and indeed the log was not shrunk.

But I observed that a lot of new log was generated. At a log file size of about 20GB each shrink attempt generated a few hundred MB of new log. I repeated the shrink operation a few times and received the same result each time.

Reading out the log I found that all of that space was for LOP_SHRINK_NOOP log records. The web does not have information about this log record type.

DBCC LOGINFO shows that there is a mix of active and unused VLFs. There is one used VLF at the end of the file ( Status = 2).

Why does attempting to shink the log sometimes...

  1. ...cause lots of new log data?
  2. ...not shrink the log file although apparently work was performed on it?
  3. ...generate LOP_SHRINK_NOOP records? What does that log record type mean and when is it expected to appear?

The statement used was:

DBCC SHRINKFILE('logfilename', 8192) --shrink to 8GB

Log file size on disk: 20GB

Log allocated size as reported by DMV: ~2GB

The log was not backed up prior to performing the SHRINKFILE. After performing a log backup the shrink succeeded.

  • What command did you use? Did you give it a reasonable smaller size or did you just say 1 MB as in SHRINKFILE(file, 1)? What size was it? Did you back the log up first? Did you back the log up twice to ensure it wrapped around? Is it possible that there were a lot of attempts to shuffle data around that failed because of open transactions etc.? – Aaron Bertrand Feb 4 '15 at 14:30
  • @AaronBertrand I appended more information. I do not think I backed up the log twice (did not know about that technique). All transactions running at that point in time probably have been short-lived. – usr Feb 4 '15 at 14:44
  • 2
    Did you ever get to the bottom of this? Similar question here dba.stackexchange.com/q/140553/3690 – Martin Smith Jun 6 '16 at 20:01
  • 1
    @MartinSmith No, never found out. My current guess: The NOOP records are meant to roll the log over but there is a bug in this rarely executed code path so it does not actually roll it over. – usr Jun 6 '16 at 20:46
  • 1
    In case somebody is not aware: "NOOP" means "no-op" which means "no operation". This is a programmer term that means "This operation does nothing but it has to happen because of some implementation choice". – usr Jun 6 '16 at 20:54

Your Answer

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

Browse other questions tagged or ask your own question.