"tulip" wrote:

> 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? we are not doing backup t-log. 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 )
>

"FM" wrote:

> Hello,
>
> Refer following link:
>
> ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/e02b2318-bee9-4d84-a61f-2fddcf268c9f.htm
>
> You can play with target_size.
>
> "tulip" wrote:
>
> > 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? we are not doing backup t-log. 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 )
> >

"Aaron Bertrand [SQL Server MVP]" wrote:

> > 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? we are not doing backup t-log. thanks
>
> Are you using simple recovery or full recovery?  If you are using full
> recovery, why are you not backing up the transaction log?  If you don't care
> about restoring to a point in time, then why not just use simple recovery?
> You really shouldn't be shrinking the transaction log like this, what is the
> point if it is going to balloon to 10GB again next weekend?  What did you
> save except to make your index rebuild take longer as it will have to wait
> for each autogrow event, because you are still going to need that space
> available to accommodate the next index rebuild?  I strongly suggest you
> read this in full, and if this is still the approach you want to take, read
> it again:
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>

"tulip" <tu***@discussions.microsoft.com> wrote in message
news:E9CA91C6-63A3-4793-83EB-AA919FD4E687@microsoft.com...
> we are using full recovery. We are doing log shipping, so every 15
> mins we do
> backup t-log. We have this db like 140g, then rebuild index over the
> weekend
> create a t-log 50g. So if we do the backup t-log every 15 mins,
> shouldn't the
> log file size shrink after that? thanks
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> > 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? we are not doing backup t-log. thanks
>>
>> Are you using simple recovery or full recovery?  If you are using
>> full
>> recovery, why are you not backing up the transaction log?  If you
>> don't care
>> about restoring to a point in time, then why not just use simple
>> recovery?
>> You really shouldn't be shrinking the transaction log like this,
>> what is the
>> point if it is going to balloon to 10GB again next weekend?  What
>> did you
>> save except to make your index rebuild take longer as it will have
>> to wait
>> for each autogrow event, because you are still going to need that
>> space
>> available to accommodate the next index rebuild?  I strongly
>> suggest you
>> read this in full, and if this is still the approach you want to
>> take, read
>> it again:
>>
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>>
>>

> 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? we are not doing backup t-log. 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 )