March 22, 2011 at 11:02 am
Now that NoTruncate and Truncateonly is no longer supported with backup's I was a bit curious to find what it exactly does when used with shrink command.I did a small test and found that it hardly made any difference.
Create database Test
GO
USE test
GO
create table t(id int identity,nm varchar(20))
GO
Declare @var int=1
while @var<=100000
begin
insert into t(nm)
select char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33) +
char(94 * RAND() + 33)
set @var=@var+1
end
GO
update T set nm=char(36 + RAND()) where ID%2=0
GO
dbcc sqlperf(logspace)
GO
dbcc shrinkfile(Test_log,truncateonly)
GO
dbcc sqlperf(logspace)
GO
--Now delete the database Test and repeat all the steps over again to be tested for notruncate option right from creation of the database and making the log file full.
dbcc shrinkfile(Test_log,notruncate)
GO
dbcc sqlperf(logspace)
GO
My intial assumption was that truncateonly releases the log space to OS while notruncate does not but makes the empty files to be moved to the end of file.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 11:15 am
On log backups, TruncateOnly is not supported any longer, but NoTruncate most certainly is.
Truncate Only: Do not back the log up to disk. Do truncate the log. Not valid in SQL 2008. Replacement: Simple recovery
NoTruncate: Do back the log up to disk. Do not truncate the log. Used for tail-log backups when the data file is damaged. See Tail-log backups in SQL BoL.
The two options on log backups do completely and totally different things to what they do on Shrink File. Both are still supported on Shrink file/DB
On shrink file/DB
Truncate Only: Release free space at the end of the data file back to the OS. Do not rearrange any data pages.
NoTruncate: Rearrange data pages so that all the free space is at the end of the file. Do Not release this space back to the OS.
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
March 22, 2011 at 11:29 am
Yes sorry I should had rephrased my statement to "TruncateOnly no longer supported with log backups'.
But with the definitions you provided does not seem to be exactly doing the same thing with the findings of test I ran.I have posted the query I used.
The log size seems to be set to the same value after shrinking when used with notruncate and truncateonly options.
Also I did make the auto grow disabled for the log file before running the test.
So what am I missing here ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 11:39 am
What you're missing is that truncateonlyon a shrink is only a valid option when shrinking a data file. Hence all my comments about moving data pages around.
See Books Online.
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
March 22, 2011 at 12:03 pm
Ok now I understood.
I thought that truncateonly when used with shrink command for log files releases the inactive VLF's which are at the end of the log file to the OS .
But how does it help and what exactly happens if we use NoTruncate with backup command for tail log backup's if the log entries are by default done sequentially in VLF ?
Sorry for being such a pain :unsure:
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 12:12 pm
Sachin Nandanwar (3/22/2011)
But how does it help and what exactly happens if we use NoTruncate with backup command for tail log backup's if the log entries are by default done sequentially in VLF ?
Huh?
As I said above:
On log backups, TruncateOnly is not supported any longer, but NoTruncate most certainly is.
Truncate Only: Do not back the log up to disk. Do truncate the log. Not valid in SQL 2008. Replacement: Simple recovery
NoTruncate: Do back the log up to disk. Do not truncate the log. Used for tail-log backups when the data file is damaged. See Tail-log backups in SQL BoL.
Got absolutely nothing to do with shrink, got nothing to do with sequential nature of the log.
The 2 options on backup log and shrink file are similar in name ONLY. Truncate as applied to a log has nothing to do with truncate on a shrink.
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
March 22, 2011 at 12:22 pm
Ok Ok Ok.Now I got it.
Sorry for getting this all mixed up.I was expecting the same behavior both on log and the datafiles when shrinking with these 2 options.
One last confusion so what exactly happens internally in the log file when you truncate it.I mean the active and inactive VLF's.Does it cause any movements or rearranging of the VLF's in the log file ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 12:28 pm
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
March 22, 2011 at 12:44 pm
GilaMonster (3/22/2011)
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Thanks for the link.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 30, 2015 at 2:45 am
Hi GilaMonster
I'm researching this and I think TuncateOnly is useful on log files as well
"The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file."
https://msdn.microsoft.com/en-us/library/ms189493.aspx
That comment is not there in the 2008 version of the docs.
Thanks for your insights
January 30, 2015 at 3:12 am
tom.pester (1/30/2015)
I'm researching this and I think TuncateOnly is useful on log files as well
"The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file."
https://msdn.microsoft.com/en-us/library/ms189493.aspx%5B/quote%5D
Oh look, Books Online is wrong again (or at least misleading)
Nothing moves log records around in a log file. Nothing can because the log sequence number is made up in part of the log record's position in the file and if a LSN ever changed it will break things catastrophically. Hence a shrinkfile will not move information around in a log, regardless of the settings.
The only difference between shrinkfile with truncateOnly and ShrinkFile without when shrinking a log is that truncateonly shrinks as much as possible (which is generally not what you want) and shrinkfile without allows a target size to be specified
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
January 30, 2015 at 5:57 am
seems to be different between 2008R2 documention and 2012 documentation,
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2015 at 6:05 am
The statement is technically correct. Shrinkfile on a log file does not move information around in the log when truncateonly is specified. The problem is that it's easy to assume from that statement that when TruncateOnly is not specified then information is moved around in the log, when it isn't.
I suspect that was added because the way it was written in old Books Online could be read that ShrinkFile with truncate only didn't work at all on a log file, which was also not true.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply