Hello My Friends, I'm newbie in SQL... [] I have one database, the size of MDF file is only 21 MB. But The LDF File is almost 200 MB. Why...? Is there any idea to reduce the LDF file??? What will happen if the LDF file is reduce ...? Thanks very much and Regards, Fauzan
Read about taking care of the log file in Books Online. The log file may growth for several reasons and there is no need to worry about this at all as long as you take care of it. Amongst other things the log records all information for data modification operations between two backups. So, if you have an active database with a lot of modifications, the log will growth to accomodate for this. If this happens regularly, I would resize the log to a reasonable size + some buffer, because every file growth operation is quite expensive in that it takes time, leads to file fragmentation and may slow down overall performance. There is no point in shrinking the file just to free up some disk space, because you may sacrifice good performance just for some extra MB's. If you are that short of disk space, you would probably want to go for more disk space.
As mentioned by Frank refer Books On Line about Transaction Log and once you understand it refer this link http://www.sql-server-citation.com/2009/08/how-to-get-rid-off-from-transaction-log.html
Dear Fauzan Create this stored procedure(Author Andrew) and run it regulary as and when you want to shrink log file CREATE proc sp_force_shrink_log -------------------------------------------------------------------------------- -- modified for SQL Server 2000 -- Purpose: Shrink transaction log of the current database in SQL Server 7.0. -- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000 -- zanevsky@azdatabases.com -------------------------------------------------------------------------------- @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 varchar(50) ) create unique clustered index loginfo_FSeqNo on #loginfo1 ( 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 print 'Inizio.' 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 #logfiles1 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 Regards Ravi
Hello Ravi, Thank you... that's very interesting... By the way, the procedures running and it reduce the LDF. But, after i execute it... there's an error. Server: Msg 1906, Level 11, State 1, Procedure sp_force_shrink_log, Line 36 Cannot create an index on '#loginfo1', because this table does not exist in database 'MyDatabase'. Any idea...?
Hi Fauzan I guess what satya is saying is r8 you have to understand what is log file and how to shrink it properly, Best way to take full backup, SQL automatically shrink your log file.. Regards Ravi
Fauzan Have you gone through that script and understand thoroughly about what it does, I hope not. You need to understand the transaction log architecture in order to manage the file sizes, http://sqlserver-qa.net/blogs/tools/archive/2007/06/19/transaction-log-guidelines.aspx fyi and see related http://sqlserver-qa.net/blogs/tools/archive/tags/transaction log/default.aspx posts.