[dba-SQLServer] Truncate a log file from code

Here is a much simpler way on SQL Server to truncate the log.

Replace PT_Data_Org with you database name and run it in each database.
And, no, you do not have to do a backup first.

CREATE PROCEDURE [dbo].[__TruncateLog]
AS
BEGIN
     ALTER DATABASE PT_Data_Org
     SET RECOVERY SIMPLE
END
BEGIN
     EXEC sp_dboption 'PT_Data_Org','trunc. log on chkpt.','TRUE'
END
BEGIN
     CHECKPOINT
END
BEGIN
     DBCC SHRINKFILE (PT_Data_Org_Log,1)
END
BEGIN
     EXEC sp_dboption 'PT_Data_Org','trunc. log on chkpt.','FALSE'
END
BEGIN
     ALTER DATABASE PT_Data_Org
     SET RECOVERY FULL
END


Robert

At 12:00 PM 12/5/2007, you wrote:
>Date: Tue, 4 Dec 2007 22:59:30 -0800
>From: "Francisco Tapia" <fhtapia at gmail.com>
>Subject: Re: [dba-SQLServer] [dba-VB] Truncate a log file from code
>To: dba-sqlserver at databaseadvisors.com
>Message-ID:
>         <b874372a0712042259w5cbaf6e3y3116819718ea539f at mail.gmail.com>
>Content-Type: text/plain; charset=ISO-8859-1
>
>One thing that I find very useful with ss is that you can schedule
>these kinds of tasks with alerts. I am not on my pc right now but
>tomorrow I can post some examples of what I typically do.
>
>Ie, I set the tlog to auto backup everytime it reaches 60% of its
>size. I also have a second alert set up so when the tlog grows beyond
>10gb I have it auto backup/shrink
>
>Hth
>
>
>
>
>On 12/3/07, James Barash <James at fcidms.com> wrote:
> > No, this is the script that creates the stored procedure. Copy it 
> into Query
> > Analyzer for 2000 or a New Query in Management Studio for 2005 select the
> > appropriate database and execute. That will create the stored 
> procedure. You
> > only need to create it once per database. Once you've created it, you just
> > need to execute it. If you are using VB.Net:
> >
> > Dim Cmd as Data.SqlClient.SqlCommand
> > Cmd.Commandtext = "sp_force_shrink_log"
> > Cmd.CommandType = CommandType.StoredProcedure
> > Cmd.Connection = conn
> > Cmd.ExecuteNonQuery()
> >
> > This assumes you already have a connection (conn) to the database.
> >
> > James Barash
> >
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> > Sent: Monday, December 03, 2007 3:20 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Truncate a log file from code
> >
> > I assume you manually copy this stuff into a SP and then edit it each time?
> >
> >
> > John W. Colby
> > Colby Consulting
> > www.ColbyConsulting.com
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of James
> > Barash
> > Sent: Monday, December 03, 2007 2:48 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Truncate a log file from code
> >
> > John:
> >
> > Below is a stored procedure I downloaded from somewhere that does exactly
> > what you want, forces a truncate of the log file. I use it in a system that
> > also imports large text files, often 10-15 million records. Use the code
> > below to create the stored procedure in the target database and 
> then execute
> > it after each import. As I recall, you are using .Net to do your import so
> > that should be easy to code. I've used it in SQL Server 2000 and 2005
> > without any problems.
> >
> > James Barash
> >
> >
> > /* Start Code */
> > CREATE proc sp_force_shrink_log
> >
> >
> > @target_percent tinyint = 0, @target_size_MB int = 10, 
> @max_iterations int =
> > 1000, @backup_log_opt nvarchar(1000) = 'with truncate_only' as set nocount
> > on
> >
> > declare @db sysname, @last_row int, @log_size decimal(15,2), @unused1
> > decimal(15,2), @unused decimal(15,2), @shrinkable decimal(15,2), @iteration
> > int, @file_max int, @file int, @fileid
> > varchar(5)
> >
> > select @db = db_name(), @iteration = 0
> >
> > create table #loginfo ( id int identity, FileId int, FileSize 
> numeric(22,0),
> > StartOffset numeric(22,0), FSeqNo int, Status int, Parity smallint,
> > CreateTime
> > nvarchar(255)  )
> >
> > create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo,
> > StartOffset )
> >
> > create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
> > insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles
> > where status & 0x40 = 0x40 select @file_max = @@rowcount
> >
> > if object_id( 'table_to_force_shrink_log' ) is null exec( 'create table
> > table_to_force_shrink_log ( x nchar(3000) not null )' )
> >
> > insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity,
> > CreateTime ) exec ( 'dbcc loginfo' ) select @last_row = @@rowcount
> >
> > select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when
> > Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case
> > when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) /
> > 1048576.00 from #loginfo
> >
> > select @unused1 = @unused -- save for later
> >
> > select 'iteration' = @iteration, 'log size, MB' = @log_size, 'unused log,
> > MB' = @unused, 'shrinkable log, MB' = @shrinkable, 'shrinkable %' 
> = convert(
> > decimal(6,2), @shrinkable
> > * 100 / @log_size )
> >
> > while @shrinkable * 100 / @log_size > @target_percent and @shrinkable >
> > @target_size_MB and @iteration < @max_iterations begin select @iteration =
> > @iteration + 1 -- this is just a precaution
> >
> > exec( 'insert table_to_force_shrink_log select name from sysobjects delete
> > table_to_force_shrink_log')
> >
> > select @file = 0 while @file < @file_max begin select @file = @file + 1
> > select @fileid = fileid from #logfiles where id = @file exec( 'dbcc
> > shrinkfile( ' + @fileid + ' )' ) end
> >
> > exec( 'backup log [' + @db + '] ' + @backup_log_opt )
> >
> > truncate table #loginfo insert #loginfo ( FileId, FileSize, StartOffset,
> > FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' ) select
> > @last_row = @@rowcount
> >
> > select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when
> > Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case
> > when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) /
> > 1048576.00 from #loginfo
> >
> > select 'iteration' = @iteration, 'log size, MB' = @log_size, 'unused log,
> > MB' = @unused, 'shrinkable log, MB' = @shrinkable, 'shrinkable %' 
> = convert(
> > decimal(6,2), @shrinkable
> > * 100 / @log_size ) end
> >
> > if @unused1 < @unused select 'After ' + convert( varchar, @iteration ) + '
> > iterations the unused
> > portion of the log has grown from ' + convert( varchar, @unused1 
> ) + ' MB to
> > ' + convert( varchar, @unused ) + ' MB.' union all select 'Since the
> > remaining unused portion is larger than 10 MB,' where @unused > 
> 10 union all
> > select 'you may try running this procedure again with a higher number of
> > iterations.' where @unused > 10 union all select 'Sometimes the log would
> > not shrink to a size smaller than several Megabytes.' where @unused <= 10
> >
> > else select 'It took ' + convert( varchar, @iteration ) + ' iterations to
> > shrink the unused portion of the log from ' + convert( varchar, 
> @unused1 ) +
> > ' MB to ' + convert( varchar, @unused ) + ' MB'
> >
> > exec( 'drop table table_to_force_shrink_log' )
> >
> > GO
> > /* End code */
> >