500 easy points - what is LOP_SHRINK_NOOP ?

Using the DBCC LOG command, I carefully monitored the size of a particular database log.

4:27 PM - 32,133
4:34 PM - 33,169
4:36 PM - 33,500
4:42 PM - 35,143
4:45 PM - 2,008,661

Seems like between 4:42 and 4:45, the log gets PACKED with the following row:

OPERATION= LOP_SHRINK_NOOP          CONTEXT=LCX_NULL

The question is, what is LOP_SHRINK_NOOP ?    I can't find information on this anywhere.


LVL 6
JaffaKREEAsked:
Who is Participating?
 
AustinSevenConnect With a Mentor Commented:
I doubt that anything can be gained from the description table.   This is definitely 'internal' stuff that isn't really documented anywhere (not that I can find anyway).    My guess is that something has grown the transaction log and the database has the auto shrink property enabled.   The shrink operation is itself a logged operation and this would explain the entries referred to.    Personally, I don't think it's necessary to use DBCC Log to monitor tran log size.  What's more useful is to use sp_spaceused.  

AustinSeven
0
 
mastooConnect With a Mentor Commented:
You might try:

dbcc log( 'databasename', 4 )

and see if you get anything in the description column
0
 
SQL_StuCommented:
I've never seen anything like LOP_SHRINK_NOOP - sounds like a joke!!

Anyway, to me it sounds like your database is corrupt.  I'd try a DBCC CHECKDB before you go any further!!
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
JaffaKREEAuthor Commented:
mastoo, that's the command I used.  There is no description for the majority of operations, including this one.

Stu, I think these are low-level transaction log operations.  They aren't documented anywhere, but I thought someone might have some experience with them.  I have a pretty good idea of what this command means, but was hoping for independent verification.

0
 
AustinSevenCommented:
I meant... 'description column'
0
 
JaffaKREEAuthor Commented:
Austin, I suspect you are correct about the AutoShrink option.  I have turned this option off in the database and will see if the transaction logs continue their behavior.

I used the sp_spaceused in the database.  Is it normal to have -

db size    unallocated space
2200MB   -630MB

Is seems odd there would be a negative number in a size field.

Thanks,
JK
0
 
JaffaKREEAuthor Commented:
Windows 2000, Service pack 3.
0
 
AustinSevenCommented:
Also, are you backing up the transaction log regularly?    Normally, folks leave the the auto shrink option turned on and just make sure they backup the tran log regularly (in full recovery mode).   If you don't need point-in-time recovery, you could put the db into Simple Recovery mode and that would keep your tran log in check.    

Are you doing regular database maintenance?  Either via Maintenance Plans or through custom maintenance jobs, it would be normal to expect DBCC UPDATEUSAGE to be run.   If for some reason this isn't being done, you can get incorrect information via sp_spaceused.

Are you not on Service Pack 3a as JaffaKREE is suggesting?  select @@version (look for .760).

AustinSeven
0
 
JaffaKREEAuthor Commented:
Austin,

   The logs are being backed up every 30 minutes as part of a log shipping maintenance plan.  The typical transaction logs for this database are about 1 MB during business hours, but every 2 hours one of them will spike enormously, as high as 260MB.  I watched the log carefully, and this spike happens all in one shot - when those LOP_SHRINK_NOOP commands get injected into the log.  This may have been stopped when I disabled AutoShrink.  I'll check on it and if there are no spikes by the end of the day, then it was almost certainly the cause.  

I'm not familiar with DBCC UPDATEUSAGE, should I expect this command to have an impact on system resources or any negative effects ?  This database is about 2.5 GB total size.

p.s.  I am JaffaKREE :-)

0
 
Partha MandayamTechnical DirectorCommented:
JaffaKREE

the -ve value you are getting for sp_spaceused is because the database usage has not been updated. This could happen if you dropped an index or something and the statistics were not updated.

Just run

sp_spaceused @updateusage = 'TRUE'
0
 
ShogunWadeConnect With a Mentor Commented:
My guess would be that your database has the AutoShrink option set.
0
 
JaffaKREEAuthor Commented:
mcp, I ran that and the usage stats have changed to the correct values.   Doesn't "auto update statistics" handle this ?
0
 
Partha MandayamConnect With a Mentor Technical DirectorCommented:
JaffaKREE

auto update statistics will update statistics when data in a table is changed but sometimes when an index is dropped the change in space is not updated unless you run the dbcc updateusage command or sp_spaceused @updateusage = 'TRUE' which corrects inaccuracies in the sysindexes table.
0
 
AustinSevenCommented:
I don't think there's much more to add but, just to be clear about it, I don't think that turning auto shrink off is necessarily the best thing to do in the long-term but, if you want to see what effect it has, then fair enough.  Anyway, I would be interested to know what you find.   Another approach you could consider is to run SQL Profiler during the times you predict the tran log will have these big growth spurts.   You might be able to see something else causing it - like long running transactions.

AustinSeven
0
 
JaffaKREEAuthor Commented:
Hey Austin,

   I've tried profiler during those time periods.  Nothing out of the ordinary.   Why do you recommend AutoShrink stay on ?   I've always read that it should be off for production systems.

0
 
AustinSevenCommented:
Auto Shrink can be on or off as you decide but it has to be part of an operational policy with regard to database maintenance.   In other words, if you turn it off, you would be advised to automatically monitor size/usage and then shrink the tran log either manually or automatically as part of a maintenance procedure.    I suppose it depends how busy you are but I would not want the task of manually monitoring tran log file size/usage.   The advantage of putting into place your own monitoring and shrinking maintenance procedure would be that you can choose a  good time to do it rather than SQL Server doing it any time it decides to do it.   For example, you could schedule a job to check and, if necessary shrink the tran log file for each database during a maintenance windows (eg. 1am).    

You can leave auto shrink off and take no further action but then you are forced to do the checking and shrinking manually or develop your own procedure to do so.    Either that or you will be leaving some large transaction files hanging around and this isn't a good thing.

One question I had for you was that if you noticed what look to be shrinkfile operations in the tran log, did the transaction log get shrunk shortly afterwards?    In other words, when you noted in your first post that the tran log suddenly grew to 2,008,661, did it shrink back shortly afterwards?   I

AustinSeven
0
 
ShogunWadeCommented:
Personally I think Autoshrink is a nasty feature.   Lets face it 99% of databases will grow over time.  So why go to the expense of shrinking all the time and having to grow all the time to compensate.   IMHO it is better to have a managed growth strategy. and manually shrink as needed.
0
 
JaffaKREEAuthor Commented:
Austin,

    Since we use log shipping, the logs are backed up every 30 minutes.  We also have an enormous amount of space on these servers, so I'm going to leave the option off for now.  Thanks for the assistance.  I never realized how heavily logged shrink and grow operations are, and probably wouldn't have if the logs weren't being shipped over a relatively slow line.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.