Francisco Tapia
fhtapia at gmail.com
Wed Dec 5 00:59:30 CST 2007
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 */ > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Saturday, December 01, 2007 12:04 AM > To: dba-sqlserver at databaseadvisors.com; dba-vb at databaseadvisors.com > Subject: [dba-SQLServer] Truncate a log file from code > > I am doing an entire directory of files, using SQLBulkCopy to import the > text files into a temp table, and from there into a permanent table. The > log file is about the same size as the data file. Is it possible to > truncate the log file after every file import in order to minimize the disk > impact? If so can you point me to example code? > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > 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...