James Barash
James at fcidms.com
Mon Dec 3 14:49:33 CST 2007
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