Francisco Tapia
fhtapia at gmail.com
Fri Dec 7 13:31:38 CST 2007
I currently deal with mission critical DBs so I need to continuously have ready backups in case of a failure. I've had hardware fail on me and have been able to restore the system up to the minute before failure. Log backups help achieve this. On Dec 7, 2007 10:45 AM, Robert L. Stewart <robert at webedb.com> wrote: > 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 */ > > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://sqlthis.blogspot.com | Tsql and More...