October 5, 2009 at 12:56 am
Matt Miller (#4) (10/4/2009)
repent_kog_is_near (10/4/2009)
Yes Sir.(btw - the correct salutation in this case would be "Ma'am". don't let Quaigon Jin fool you)
Not that "yes <anything>" is a useful answer to the question "What do you mean by ....?"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2009 at 6:18 am
oops.. my fault.. I misread and said yes to 'truncation of log ... after backing up the log' missing the what in the front.
Let me explain.
The maintenance plan first runs
BACKUP LOG [DBName] TO DISK = N'C:\BACKUP\TransLogName.TRN' WITH RETAINDAYS = 5, NOFORMAT, NOINIT, NAME = N'TransLogName_Timestamp', SKIP, REWIND, NOUNLOAD, STATS = 10
And right after that
DBCC SHRINKFILE ( 2 ,400 ,TRUNCATEONLY )
Now, I have removed the second part - SHRINKFILE. This has probably done 2 bad things
*made DB slow (is it because of fragmentation)
*made Recovery to point-in-time not possible because of TruncateOnly
What any other damange done? Other than removing the SHRINKFILE part from the maintenance, anything else I need to do to fix?
Thx
Dan
October 5, 2009 at 6:58 am
repent_kog_is_near (10/5/2009)
Now, I have removed the second part - SHRINKFILE. This has probably done 2 bad things*made Recovery to point-in-time not possible because of TruncateOnly
No, no, no!!!
There is a difference between truncating the log and shrinking the log.
"Shrinkfile" shrinks the log.
"Backup log with truncate only" truncates the log.
Shrinkfile does not and can not truncate the log. Backup log (regardless of options) does not shrink the log.
If you look in Books Online, you will notice that TruncateOnly is an ignored option for log files. It is only valid for data files.
If you've been running Shrinkfile on the transaction log then all you have been doing is shrinking the log. The reason shrinking the log file impacts performance is that when the log grows (which it will if it's been shrunk) transactions have to stop and wait for the log to grow. That is what takes the time.
Shrinking a data file causes fragmentation, which can impact performance. This is repaired by rebuilding the indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2009 at 6:00 am
Gail
Thanks for the help.
We had been trying to do this way, hoping to save space.
Do you know what is the default recovery interval, in minutes, for FULL Recovery model? If this is short enough, our worry is not worth it.
Hope in the next version, MS will throw an error if trying to use ShrinkFile for a log with truncate only option.
Dan.
October 6, 2009 at 8:02 am
repent_kog_is_near (10/6/2009)
Do you know what is the default recovery interval, in minutes, for FULL Recovery model? If this is short enough, our worry is not worth it.
Offhand I don't, but if you look in Books Online (search for 'recovery interval') it'll tell you.
What worry? I think you're still misunderstanding things.
Recovery interval affects the interval between checkpoints. The value is the desired maximum duration for restart-recovery which runs when the database is brought online, like when the server is started.
Hope in the next version, MS will throw an error if trying to use ShrinkFile for a log with truncate only option.
Why? It's not an error. If shrinkfile is run on a log and the truncateonly option is specified, then SQL completely ignores the truncateonly setting and just shrinks the log. If you look at what ShrinkFile does when the truncateonly option is set you'll see that it's only valid option on data files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2009 at 9:09 am
Gail
recovery interval seems to be around a minute. Thanks for clarifying that.
I just wished it would have given some kind of warning that we are not using the right options for ShrinkFile. Well, now I know..
🙂
Thx
Dan.
November 16, 2009 at 8:26 pm
GSquared
Had you by any chance posted the scripts you were referring to, in,
http://www.sqlservercentral.com/Forums/FindPost796533.aspx
in this site?
I am scanning through the articles section once in a while. I will wait; but just want to make sure I am not looking in the wrong place.
thanks
Dan
November 17, 2009 at 3:26 am
Recovery interval:
SQL Server makes an internal estimate of the number of data modifications it could roll forward (re-do) from the log in the time allowed (in minutes). When set to the default of zero, SQL Server uses one minute as the time interval in that estimate. Let's call the estimate it makes 'N', and assume that the recovery interval is left set at the default of zero (recommended!)
SQL Server then checks the log of every database on the instance every minute, and only if there are more than 'N' data modifications in the log will SQL Server perform a checkpoint. (Though it will also issue a checkpoint if the log becomes 70% full).
So, the frequency of checkpoints depends on SQL Server's estimate, the number of data modifications, and the fullness of the log. It does not run on a fixed schedule.
SQL Server also tries not to issue a checkpoint more often than the number of minutes specified by the recovery interval.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply