Solved

500 easy points - what is LOP_SHRINK_NOOP ?

Posted on 2004-10-11
18
721 Views
Last Modified: 2008-01-09
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.


0
Comment
Question by:JaffaKREE
  • 7
  • 5
  • 2
  • +3
18 Comments
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 50 total points
ID: 12277332
You might try:

dbcc log( 'databasename', 4 )

and see if you get anything in the description column
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12277350
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12277378
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
 
LVL 10

Accepted Solution

by:
AustinSeven earned 300 total points
ID: 12277423
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12277426
I meant... 'description column'
0
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12277501
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12277534
Windows 2000, Service pack 3.
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12277809
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12277881
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 6

Expert Comment

by:mcp111
ID: 12278164
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 50 total points
ID: 12278691
My guess would be that your database has the AutoShrink option set.
0
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12279083
mcp, I ran that and the usage stats have changed to the correct values.   Doesn't "auto update statistics" handle this ?
0
 
LVL 6

Assisted Solution

by:mcp111
mcp111 earned 100 total points
ID: 12279144
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12280759
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12280893
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12285008
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12285258
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 12286371
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now