InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:32:39 GMT20http://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxThat would work better than the order you originally had, yes.Sun, 13 Jan 2013 12:54:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote]a checkpoint after a log backup will not mark additional log records as reusable and hence will not allow a shrink to reclaim more space than it would if the checkpoint had not been run after the log backup.[/quote]Totally agree.So, the order would be:1) checkpoint2) BACKUP LOG MYDatabase TO DISK='D:\transactionLogBackup1.trn'3) DBCC SHRINKFILE('MyTransactionLogName', 1000) -- 1000 MB. Set as you like.Repeat 1-3 until shrinkedSun, 13 Jan 2013 11:00:25 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]Vedran Kesegic (1/13/2013)[/b][hr]But, it turns out that checkpoint indeed occurs even at the beginning of a log backup, not just full/diff backup.[/quote]It can occur. It's not an automatic thing that always happens, but it can (I've seen checkpoints triggered by a log backup when either traceflags or server settings have resulted in a lack of automatic checkpoints).[quote]But the checkpoint (better to say: the lack of it) can prevent VLF to be cleared (when clearing process is triggered by log backup).[/quote]Of course it can. What I'm saying, and have been saying, is that a checkpoint after a log backup will not mark additional log records as reusable and hence will not allow a shrink to reclaim more space than it would if the checkpoint had not been run after the log backup.Sun, 13 Jan 2013 09:38:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxI tried to setup excercise in full recovery model that shows in log truncation that occured no VLF's are marked inactive because the lack of the CHECKPOINT.But, it turns out that checkpoint indeed occurs even at the beginning of a log backup,not just full/diff backup. At least in SQL2012 instance i tried that.Here it is, try it:[code="sql"]-- We will prevent file growth by setting maxsize equal to initial sizecreate database TestCKPT ONPRIMARY( NAME = data, FILENAME = 'D:\temp\TestCKPT.mdf', SIZE = 100 MB, MAXSIZE = 100 MB)LOG ON( NAME = log, FILENAME = 'D:\temp\TestCKPT.ldf', SIZE = 65544 KB, -- 64MB + 1 page MAXSIZE = 65544 KB)GO-- We wont to control when checkpoint occur. So, we will disable automatic checkpoins by trace flag 3505-- to prevent automatic checkpoint occur for duration of the test.-- Alternative would be increasing the RECOVERY INTERVAL parameter.EXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'recovery interval', 32767GORECONFIGURE WITH OVERRIDEGOALTER DATABASE TestCKPT SET TARGET_RECOVERY_TIME = 3600 MINUTESGO-- Pu full recovery model in effectUSE TestCKPTalter database TestCKPT SET recovery FULL-- Until we take a full backup, we are effectively in SIMPLE recovery model,-- even if sys.databases shows it is in FULL.-- So let's take a full backup to kick-start full rm:BACKUP DATABASE TestCKPT TO DISK = 'D:\temp\TestCKPT.bak'backup log TestCKPT TO DISK = 'TestCKPT.ldf'GO-- Checkpoint location is in VLF that is first part of three-part LSN.-- That is 21 in our case:SELECT DB_NAME() AS DatabaseName, [Current LSN], [Previous LSN], Operation, [Checkpoint Begin], [Checkpoint End], [Dirty Pages] FROM fn_dblog(NULL, NULL) WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')-- This is a list of all VLF's (one row equals one VLF).-- We can see that active VLF is the one with VLF seqence number 21 (the one that checkpoint is)declare @vlfs table( RecoveryUnitId int, -- sql2012 only FileId int, FileSize bigint, -- VLF size in bytes StartOffset bigint, -- VLF offset in bytes from beginning of transaction log FSeqNo int, Status int, Parity tinyint, CreateLSN decimal(25,0))insert into @vlfsexec('DBCC LOGINFO () WITH TABLERESULTS, NO_INFOMSGS')SELECT VLF_SeqNo = convert(varbinary(4),v.FSeqNo), file_id = v.FileId, logical_name = f.name, f.physical_name, log_size_kb = REPLACE(CONVERT(varchar, f.size*$8, 1), '.00', ''), vlf_size_kb = REPLACE(CONVERT(varchar, v.FileSize/$1024, 1), '.00', ''), vlf_physical_offset = v.StartOffset, WriteSequenceNo = ROW_NUMBER() OVER(ORDER BY v.FSeqNo), Status = CASE WHEN v.Status=2 THEN 'ACTIVE' ELSE 'INACTIVE (free)' END, v.CreateLSNFROM @vlfs v JOIN sys.database_files f on f.file_id = v.FileIdorder by v.StartOffsetGO-- Let's make some transaction to move log insertion point to the next VLFCREATE TABLE wide(x nchar(4000) DEFAULT 'A') -- a bit less than 8KB, one pageGOINSERT INTO wide default valuesGO 1024-- By running previous queries, confirm that we have moved to the next VLF (22) - now we have two active VLFs-- and confirm that no checkpoint has occured other than the one we saw in previous VLF (21).-- We dont have any open transactions. Will log backup mark the first VLF inactive (clear it) ?SELECT DB_NAME() AS DatabaseName, [Current LSN], [Previous LSN], Operation, [Checkpoint Begin], [Checkpoint End], [Dirty Pages] FROM fn_dblog(NULL, NULL) WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')backup log TestCKPT TO DISK = 'TestCKPT2.ldf'SELECT DB_NAME() AS DatabaseName, [Current LSN], [Previous LSN], Operation, [Checkpoint Begin], [Checkpoint End], [Dirty Pages] FROM fn_dblog(NULL, NULL) WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')-- TURNS OUT CHECKPOINT OCCURED ON TRAN LOG BACKUP!-- cleanupuse masterdrop database TestCKPTGO[/code]Sun, 13 Jan 2013 07:22:04 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxI'm well aware what triggers log truncation in simple vs full/bulk recovery model (checkpoint vs log backup). I'm also aware of the conditions that determine what VLFs will actually be cleared (if any at all), once the clearing process is triggered. Those clearing conditions are also different, depending on recovery model, is there mirroring, transaction replication, full/diff bacup in progress etc.Checkpoint does not require any log records - crash recovery process does. But the checkpoint (better to say: the lack of it) can prevent VLF to be cleared (when clearing process is triggered by log backup).Sun, 13 Jan 2013 07:18:03 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]Vedran Kesegic (1/12/2013)[/b][hr]But there is one more thing that checkpoint does to the log in regard of log truncation.Checkpoint is one of several prerequisites for log clearing. [/quote]Yes, I'm well aware of what checkpoint does.[quote]Log backup triggers that clearing, but will VLF's actually be cleared (if any at all) is determined by several prerequisites. And checkpoint is one of those prerequisites! [/quote]Correct it is (and since I wrote an article on that subject, I'm kinda familiar with the reasons why the log may not be reused)[quote]Basically, with CHECKPOINT command just prior to shrink log you may achieve a little bit more of log cleared (and thus shrinked) than without it.[/quote]Not in full or bulk-logged recovery. In simple recovery Checkpoint triggers log clearing, so your statement is true for simple recovery. In full recovery, only a log backup triggers log clearing (checkpoint does not). Hence, if at the point that you run a log backup, there are log records that are required for a checkpoint, the VLFs with those log records will not be cleared. If you then run a checkpoint, those log records are no longer needed, but the log clearing (to clear those now clearable VLFs) will not run until another log backup is run.Sun, 13 Jan 2013 01:09:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]GilaMonster (1/12/2013)[/b][hr][quote][b]muthukkumaran Kaliyamoorthy (1/12/2013)[/b][hr]Have a look [url=http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/][u]Truncating and shrinking the transaction log file[/u][/url]Also take a look @ http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/[/quote]There's a bunch of errors in both of those, just beware (but that should apply to just about anything you read on the net)[/quote]I'll take a look and correct those. Thanks Gail.Sat, 12 Jan 2013 19:07:03 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote]There's no implicit (or explicit) checkpoint that happens within the transaction log backup[/quote]I know for sure (tested and double-checked) that checkpoint is implicitly executed at the beginning of full or diff backup process, but I did not know that it is not implicitly executed at transaction log backup.And that makes sense when I think about that processes.Thanks Gail, I have learned something today. It's so good to help people and learn something in the process.[quote] in full recovery checkpoint won't do anything to the log. Only a log backup clears the log in full recovery.[/quote]Checkpoint writes to the log: checkpoint begin, checkpoint end, and list of active transactions' LSNs.But there is one more thing that checkpoint does to the log in regard of log truncation.Checkpoint is one of several prerequisites for log clearing. Log backup triggers that clearing, but will VLF's actually be cleared (if any at all) is determined by several prerequisites. And checkpoint is one of those prerequisites! That's why "CHECKPOINT" is listed under possible values of log_reuse_wait_desc in sys.databases: [url=http://msdn.microsoft.com/en-us/library/ms178534.aspx]http://msdn.microsoft.com/en-us/library/ms178534.aspx[/url], and that reason can happen in full recovery model also.Log records after the last checkpoint are badly needed by someone: by the recovery process.See here (BOL): [url=http://msdn.microsoft.com/en-us/library/ms189085%28v=sql.105%29.aspx]http://msdn.microsoft.com/en-us/library/ms189085%28v=sql.105%29.aspx[/url]If disaster strikes, recovery process begins at the point of the last checkpoint in transaction log (page 1:9 contains that log location, and minLSN which might be prior or equal to checkpoint). Because checkpoint is a "firm point" - the actual state of data files we have on disks. Always from that point (from that checkpoint) it rolls forward the entire log, and then rolls back transactions that were active (uncommitted) at the moment of disaster. So, it needs AT LEAST the log from the last checkpoint and on (I say "at least" because active transactions can make that range even wider if they start before last checkpoint, and minLSN can be even before that checkpoint, but never after that last checkpoint). That means you can't clear the VLFs starting with the one that contains the last checkpoint and on, no matter how many times you call "backup log" command, and even if you don't have any opened transactions.Basically, with CHECKPOINT command just prior to shrink log you may achieve a little bit more of log cleared (and thus shrinked) than without it.Though, the difference is probably not huge because checkpoints happen automatically quite often (roughly once per minute, depending on "recovery interval" setting, log record generation rate, and few other factors), enabling you to clear the log with log backup.Sat, 12 Jan 2013 18:20:09 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]muthukkumaran Kaliyamoorthy (1/12/2013)[/b][hr]Have a look [url=http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/][u]Truncating and shrinking the transaction log file[/u][/url]Also take a look @ http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/[/quote]There's a bunch of errors in both of those, just beware (but that should apply to just about anything you read on the net)Sat, 12 Jan 2013 11:18:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxHave a look [url=http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/][u]Truncating and shrinking the transaction log file[/u][/url]Also take a look @ http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/Sat, 12 Jan 2013 10:48:28 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]Vedran Kesegic (1/12/2013)[/b][hr]You may benefit a little from that checkpoint step becase there is certain amount of time passed between implicit checkpoint that happens within tran log backup command and the shrink command. [/quote]There's no implicit (or explicit) checkpoint that happens within the transaction log backup, and in full recovery checkpoint won't do anything to the log. Only a log backup clears the log in full recovery.Sat, 12 Jan 2013 09:57:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxCheckpoint will not flush pages from memory. Just writes dirty pages and they stay in memory, now in clean state. No danger there. Unlike lazy writer that responds to memory pressure and certain dbcc commands that really flushes-out pages from memory. You may benefit a little from that checkpoint step becase there is certain amount of time passed between implicit checkpoint that happens within tran log backup command and the shrink command. There could be benefit when log grows really fast, and log backup takes larger amount of time, but is not something essential. Just may give you a bit better result.If you want to decrease number of VLFs, shrink to near zero, than expand to desired initial size in one step, eg 4000MB. Set the growth to fixed size as Gail said. Eg, 512MB or 256MB if your storage is not very fast. Initial size should be large enough to accomodate normal db operation without any growth.Sat, 12 Jan 2013 09:17:16 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]jonathanforster (1/11/2013)[/b][hr]Hi,Now I've started running trans log backups from SSMS so they shouldn't get any bigger but I can't run them very often (and increase the profitability of point in time recovery) because they're too big.[/quote]Once you perform a transaction log backup, the existing transaction log space is marked for re-use where applicable. The process repeats each time you perform a transaction log backup and hence why the recommended practice is to perform frequent log backups.This means that your existing transaction log files do not need to be the size that they have grown to. From what you describe, that there have been no transaction log backups until you intervened, you should be able to shrink your transaction logs (quite significantly) because currently they are sized to accommodate the history of all previous transactions.You need to perform an exercise to correctly size all of your transaction log files and you should also schedule regular log backups. To assist with this I suggest you familirise yourself with the contents of the Books Online topic [url=http://msdn.microsoft.com/en-us/library/ms345583(v=sql.105).aspx]Transaction Log Management[/url]. I hope this helps and let me know if you have any further questions.Sat, 12 Jan 2013 09:02:45 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspx[quote][b]jonathanforster (1/12/2013)[/b][hr]1) I've already run the first 80GB tlog backup and was under the impression that every subsequent tlog backup would be the same size until I used a SHRINK on the tlog? I can't efficiently test this because I would run out of space if indeed the second tlog backup was the same size. [/quote]No. A log backup will be the size of the log records backed up, not the size of the log file. Just as a database backup is the size of the data in the database, not the size of the database files[quote]2) My idea before was that after a SHRINK I could control the size of the tlog with regular tlog backups and just leave the growth increment to the default (10%). [/quote]10% is a poor choice. It leads to lots of tiny grow operations when the log is small and huge, time-consuming log growths when the log is large. Set your growth increment to a fixed size[quote]You said that I shouldn't let it grow 'dynamically' by this do you mean that I shouldn't SHRINK it to a really small size and let it grow by itself? This would create a performance issue? [/quote]No, don't do that. Shrink the log to the size needed for normal operations + some head room (yes, you may need to do some monitoring to tell that value), set a sensible increment value (fixed MB, not %)[quote]3) The advised procedure on SHRINKING the tlog after the initial 80GB tlog backup includes a CHECKPOINT. I understand this writes the 'dirty' pages from memory onto the disk. What's the benefit of doing this before the SHRINK?[/quote]In full recovery model, none. In simple that would truncate the log (mark as reusable) potentially allowing shrink to a smaller size[quote] Are there any negative impacts of running the CHECKPOINT (I'm guessing it doesn't break the backup log chain)?[/quote]Seeing as SQL runs automatic checkpoints on a regular basis during normal operations, no negative impacts.Sat, 12 Jan 2013 07:56:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxThanks for the replies, which have prompted some more questions :-) 1) I've already run the first 80GB tlog backup and was under the impression that every subsequent tlog backup would be the same size until I used a SHRINK on the tlog? I can't efficiently test this because I would run out of space if indeed the second tlog backup was the same size. 2) My idea before was that after a SHRINK I could control the size of the tlog with regular tlog backups and just leave the growth increment to the default (10%). You said that I shouldn't let it grow 'dynamically' by this do you mean that I shouldn't SHRINK it to a really small size and let it grow by itself? This would create a performance issue? 3) The advised procedure on SHRINKING the tlog after the initial 80GB tlog backup includes a CHECKPOINT. I understand this writes the 'dirty' pages from memory onto the disk. What's the benefit of doing this before the SHRINK? Are there any negative impacts of running the CHECKPOINT (I'm guessing it doesn't break the backup log chain)?Sat, 12 Jan 2013 07:41:40 GMTjonathanforsterhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxFull/diff backup does not clear transaction log ("clear" is sometimes referred as "truncate" and actually means "mark parts of tran log as free for reuse". Clearing tran log DOES NOT make tran log file smaller!). Only transaction log backup can clear transaction log. Only shrink can shrink the tran log size if certain conditions are met.Transaction log backup consists just of the log records after the previous transaction log backup. They have nothing to do with tran log size, as strange it seems! So saying "I can't run them very often because they're too big" is not true - fortunately for you. Log file size is big, but log [u]backup[/u] size is not (except the initial tran log backup which really will be around 80GB)!This is the query which will show you VLF's (virtual log files - logical units of transaction log that can be marked as "free to reuse"):[code="sql"]declare @vlfs table( --RecoveryUnitId int, -- sql2012 only FileId int, FileSize bigint, -- VLF size in bytes StartOffset bigint, -- VLF offset in bytes from beginning of transaction log FSeqNo int, Status int, Parity tinyint, CreateLSN decimal(25,0))insert into @vlfs--( RecoveryUnitId, -- sql2012 only-- FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN--)exec('DBCC LOGINFO () WITH TABLERESULTS, NO_INFOMSGS')SELECT file_id = v.FileId, logical_name = f.name, f.physical_name, log_size_kb = REPLACE(CONVERT(varchar, f.size*$8, 1), '.00', ''), vlf_size_kb = REPLACE(CONVERT(varchar, v.FileSize/$1024, 1), '.00', ''), vlf_physical_offset = v.StartOffset, WriteSequenceNo = ROW_NUMBER() OVER(ORDER BY v.FSeqNo), Status = CASE WHEN v.Status=2 THEN 'ACTIVE' ELSE 'INACTIVE (free)' END, v.CreateLSNFROM @vlfs v JOIN sys.database_files f on f.file_id = v.FileIdorder by v.StartOffset[/code]You will see which VLF's are free, and their sizes. You should not have more than 50 VLF's, so possibly you should do some tran. log optimizations after this situation is resolved.If you already did not do that very slow initial 80GB tran log backup, and in order to avoid it, AND if you can afford to be without point-in-time restore capability until this operation is finished, you can do it with this extra-fast procedure. But again, it WILL BREAK the log backup chain:- make diff backup. That is the point you can recover to if anything goes wrong. DIFF backup is much faster than initial backup of 80GB tran. log., and also much faster and smaller than full backup.- switch db to simple recovery model (that breaks the log backup chain)- checkpoint and shrink the log file (should be very fast operation)- switch db to full recovery model- make differential backup to initialize new log backup chain, and actually start behaving like full recovery model. Why diff backup and not full? Diff will be faster than full, so you get less time spent exposed in simple recovery model. From now on you take regular transaction log backups as usual, and as often is required. You can recover point-in-time from time that diff backup finished and on.SCRIPT that as a whole unit, and TEST before you run it.That is the fastest method and the process should be finished in minutes (as long as two diff backups take plus few seconds), AND you do not have to find 80GB of space for initial tran log backup.If you do not want to lose point-in-time recovery capability, and are willing to wait spending your time in a much longer process, do this procedure (no log backup chain is lost here):1) manually start the job that takes transaction log backup and wait for it to finish OR do it yourself (be careful to change filename in each iteration):BACKUP LOG MYDatabase TO DISK='D:\transactionLogBackup1.trn'This step will take a VERY long time for the initial 80GB backup. Log backups after that will be fast and small (relatively to initial log backup) even the log itself is still 80GB.2) checkpoint3) DBCC SHRINKFILE('MyTransactionLogName', 1000) -- 1000 MB. Set as you like.Repeat those 1,2,3 until the log is shrinked to desired size, probably 1-2 times will be enough.Use backup compression (if available) for all the backup steps (diff and tran log backups) to speed up the process.After that, optimize the number of VLF's, log size, and growth size, but that's another story.Good luck!Fri, 11 Jan 2013 19:52:45 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxYou can definitely shrink the log file "live". SQL simply won't do it if it would cause a loss of data.The tricky part is finding the right size to shrink it to: you don't want to shrink so much that it has to grow dynamically, but you don't want to leave it overallocated so much that it holds significant unused extra disk space. It may take a little experimentation to get the best size for each log file.Fri, 11 Jan 2013 15:55:55 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxYes you are right. You can shrink it to the reasonable size and schedule the Tlog backup to maintain the size as much as same.Fri, 11 Jan 2013 14:52:21 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic1406253-391-1.aspxHi,There's been lots of posts about shrinking databases so sorry to tread over old ground but I have a fairly specific question and am working with live production data so definitely do not want to risk anything.I've inherited the admin of an SQL server and the previous admin decided to only take full backups (using NT Backup) of the db's running in full recovery mode. The trans logs of all the databases add up to more than 80GB which is pushing the capacity on the server. Now I've started running trans log backups from SSMS so they shouldn't get any bigger but I can't run them very often (and increase the profitability of point in time recovery) because they're too big.Question is should I shrink the transaction logs so they become more manageable? Obviously they will grow again but if I run the transaction backup frequently that should keep them comparatively small and I'll get the benefits of a closer to 'now' point in time recovery.I can 'manage' with them being the size they are now so definitely do not want to risk the live data if there's a possibility it could cause problems.What's the expert opinion? Thanks in advance...Fri, 11 Jan 2013 13:55:51 GMTjonathanforster