| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2011-04-12 : 10:12:44
|
| Over time it has become clear that backing up a log to disk and then trying to shrink the log using DBCC SHRINKFILE frequently fails the first time. At first I thought I was not seeing things clearly or perhaps it only does that occasionally. but it's true. If I then backup the log to disk a second time (goes very fast and creates a tiny backup file) then the log shrinks quite happily. Anybody got a clue why that is? Does the second backup somehow push status 2 logical logs to the front of the file? That would be wierd. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-12 : 10:21:24
|
| You should not be regularly shrinking your log files (or any file, for that matter). If you back them up regularly there will be minimal growth. The only reason to shrink a log file is if you're almost out of disk space, which is a larger problem. All shrinking does is generate needless work for SQL Server, since they'll grow back to a larger size anyway. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-12 : 10:52:02
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-04-12 : 11:19:27
|
| I anticipated the rebuke about shrinking logs. Thanks, but I am still curious about why a backup/shrink takes two cycles. Do you not think that curious? Does not Microsoft suggest that one cycle should be sufficient? Something happens physically in this process. If you don't understand it... that's ok. Maybe somebody else does. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-12 : 11:44:23
|
quote:
If you don't understand it... that's ok. Maybe somebody else does.
I understand exactly how it works and why that behavior exists. I'm more concerned that you're following a bad practice and you should discontinue it. If you read about transaction logs in Books Online, or the article Gail posted, you'd understand too, as well as why shrinking is not recommended.Perhaps you should indulge your curiosity in actual research rather than complaining about why no one is explaining it, and suggesting others don't understand it. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-04-12 : 12:44:17
|
| That article explains why a transaction log should not be truncated. The word shrink does not appear. There are a lot of "should"s and "shouldn't"s in our line of work. One of them is, "Programmers should take commits once in a while." Sometimes you just ain't got no control over programmers. Another one is "Management should give the DBA all the disk space he thinks he needs". Sometimes you just ain't got no control over management. Sometimes you gotta shrink. Any more hints on what I should read? I've read a lot about logs. So far I've seen no explanation why one backup to disk and one shrink isn't gonna do the job. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-12 : 12:47:43
|
| http://msdn.microsoft.com/en-us/library/ms189573.aspx |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-12 : 17:01:41
|
| Sometimes it is acceptable to shrink - after some operation has grown the log beyond normal. If you're shrinking daily, the log is just going to grow back that size. Rather make the log backups more frequent (if log backups are the limiting item on log reuse, normally is) and let the log reach a steady size and stay there.By repeatedly shrinking and growing you are hindering log performance. Your transactions will be slower, your log backups will be slower, your crash recovery and restores will be slower. (google VLF)--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-04-15 : 16:43:04
|
| Tara, I don't think so. That speaks mainly about datafile shrinking. I notice this only with logs. I did a test. I found a log (we have lots of SQL instances and lots-and-lots of databases) that was 34 gig and full recovery mode. I backed it up to disk. I shrunk it with DBCC SHRINKFILE(2,500). It was shrunk about one gig, size now = 33 gig. I backed it up to disk again. This time it went very fast. I issued the shrink command again. Now the log is 505 MB. The first backup to disk was 34 gig. The second backup to disk was 22 MB. I made certain that no other backup processes were active. I see this behavior on SQL 2000, 2005, and 2008. Both backups were successful. both shrinks finished without errors. I invite those who know what is going on here to offer some enlightenment. I've learned to live with this, but I sure don't understand it. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-15 : 19:13:37
|
| This all has to do with what VLF's in the log file are currently marked as in use. The first time you shrink - it shrinks down to the last VLF file that is marked active. When you run another log backup, the active VLF's are rolled to the beginning of the file and your next shrink is successful.To see this in action, run DBCC LOGINFO in the database before doing a shrinkfile. Then, run it again after the shrinkfile and you will see that the last VLF is marked as inuse (2). Run another log backup - then run DBCC LOGINFO again to see what VLF's are active.You can only shrink the log file down to last active VLF.BTW - why would you be doing this on a regular basis anyways?Jeff |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-18 : 17:33:56
|
| Its depend on CHECKPOINT;Regards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-04-19 : 11:50:04
|
| Thanks, Jeff, This is not something I do on a regular basis. I suspected it might have something to do with the status 2 vlf placement, but it didn't make sense to me that they would roll to the front on the second backup, but not on the first. Riv and Rern. I'll try the DBCC LOGINFO next time to track things. Thanks. Syed, What depends on checkpoint? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-19 : 14:15:24
|
| It depends where the active VLFs are. They won't be moved around when the log truncates.Checkpoint is what truncates the log. In full and bulk-logged it requires a log backup as well, and for nothing to be needing the log records. Log backups can automatically run a checkpoint, but don't always.--Gail ShawSQL Server MVP |
|
|
|
|
Backup Shrink Backup Shrink Why?