December 11, 2008 at 2:41 pm
when you specify dbcc shrinkfile notruncate or dbcc shrinkfile truncateonly, how do you know that the transaction log file would shrink to what size? we had a rebuild index job run every weekend, after that, the t-log become 10G, then we have bat file to do the shrink (now the t-log is only 138M), the command is like this ( i got from the bat file), so how does the t-log know what size to shrink to? thanks
-- tell the file to reorganize (squeeze out the holes)
select @cmd = 'use [' + @dbname+']'+char(10)+'dbcc shrinkfile('''+@logname+''',NOTRUNCATE) WITH NO_INFOMSGS'
execute( @cmd )
-- now we can truncate the file to remove the one big hole
select @cmd = 'use [' + @dbname+']'+char(10)+'dbcc shrinkfile('''+@logname+''',TRUNCATEONLY) WITH NO_INFOMSGS'
execute( @cmd )
December 11, 2008 at 10:05 pm
[font="Verdana"]
In your case the 'target_size' parameter is not specified. So, the SHRINKFILE reduces the size of the file to the default file size or the last boundary of the allocated VLF.
From BOL:
The physical log file can never be smaller than the amount of space currently used within the log file. You can use the DBCC SQLPERF (LOGSPACE) command to monitor the amount of space used.For more information, see the "Virtual Log Files" in SQL Server Books Online.
Because a log can be shrunk only to a virtual log file (VLF) boundary, it is not possible to shrink a log file to a size smaller than a VLF even if the space is not being used. Likewise, if a portion of a VLF is in use you cannot shrink any of the space in that VLF. For more information, see the "Transaction Log Physical Architecture" topics in SQL Server Books Online.
For more information, see the Shrinking the Transaction Log and DBCC SHRINKFILE topics in SQL Server 2000 Books Online.
Regards,
[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply