| Author |
Topic |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
 Posted - 2008-12-03 : 15:19:59
|
| Can anyone explain what the LOP_SHRINK_NOOP is in the transaction log? In particular, why would it occur 7,500,000 times in a couple of weeks and result in a 100GB transaction log - in database that is not being used?! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-12-03 : 15:47:47
|
It's not in a message, it's in output from ::fn_dblog. I ran this queryselect top 100 * from ::fn_dblog(null, null)where operation = 'LOP_SHRINK_NOOP' I did this because we have one database that is not in use but has run up a 100GB log in the past two weeks. We're not backing it up so the log isn't getting truncated. I can solve this easily enough by setting the database to use simple recovery, but I'm fascinated by the fact that this would happen, or perhaps I say terrified?Almost all the columns from ::fn_dblog are NULL but the log record for every one of the 7,500,000 of them is 12316 bytes so that make for a very large log file in a hurry. A Google search for LOP_SHRINK_NOOP returns only two hits! |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-12-03 : 15:55:40
|
| Is this database set to auto-shrink? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-12-04 : 00:34:39
|
quote:
Originally posted by mcrowley
Is this database set to auto-shrink?
Yes, auto create statistics, auto shrink, and auto update statistics are all on.Some more info - the database itself is 162MB, the log is 92GB and growing, but the database is not in use. I ran the growth/shrink events report and I see Log File Auto Growth and Log File Auto Shrinks happening every two or three hours, so I'm guessing that SQL Server has discovered a great way to keep itself busy, growing the log files by keeping record of growth and shrink events in a database that isn't ever using its log!7,500,000 transactions in a couple of weeks in the name of automated database management seems a little extreme! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-04 : 01:21:00
|
When was last time you did a backup of your database?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-12-04 : 09:32:00
|
| Remove auto-shrink. It ain't gettin any smaller. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-04 : 09:36:28
|
quote:
Originally posted by mcrowley
Remove auto-shrink. It ain't gettin any smaller.
On top of that, You must have rebuild index job that sweeps the trash(Fragmentation) left by Shrinking databse |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-12-04 : 13:25:38
|
quote:
Originally posted by Peso
When was last time you did a backup of your database?
I haven't backed it up for a long time because I'm not using the database.Just to clarify here - I'm not asking how I can make the log smaller, I know how to do that. I'm trying to see if anyone can add any insight as to why SQL Server will grow a log to 100GB for a database that is not being used, and I guess I've established that a database with auto shrink set on will actually grow larger not smaller if it is not actively being backed up. That's hardly intuitive, but I guess having a database that you're not using makes no sense either!  All in all it explains a lot though for people that report a database with a log that is growing out of control.To summarize:To prevent your database (specifically the log, not the database) growing at an alarming rate you must do one of the following:1. Run regular backups.2. Set the database to use the simple recovery model.3. Turn auto shrink OFF.NOTE: The preference is to be running backups regularly, and you must fully understand the implications of setting the database to use the simple recovery model if you choose that option. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-12-04 : 13:33:15
|
| There are a lot of reasons to keep auto-shrink off. Especially on databases that are in use. |
|
|
|
|
What is LOP_SHRINK_NOOP?