Transaction log truncation
-
Hi
I have a huge transaction log. I am not able to truncate the same.
I have run "DBCC SHRINKFILE" multiple times with no effect.
Recently when going through the forums I had come across a script which truncates the transaction log. I am not able to find the same. Could somebody please repost the script.
I know I know My search skills are pretty bad
Rgds
Kudla
-
CHECKPOINT
GO
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
CHECKPOINT
GO
USE YourDatabase
GO
EXEC sp_helpfile
One you have run the above you should be able to use your DBCC SHRINKFILE on the files listed.
-
If you can't use TRUNCATE_ONLY Option
You may want to run the following command instead:
BACKUP LOG [Databasename] WITH NO_LOG
The NO_LOG clause removes the inactive part of the log without making a backup copy of it, and saves space by not logging the operation.
-
I have used this script many times and it works good. (I think I found it here on sqlservercentral).
/Joel
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
-
Backing up the transaction log shrinks the active portion (I believe - I'm new at this). Have a regular t-log backup in your maintenance plans.
-
If you need this disk space urgently, you can use a rather brute-force method.
1. detach db
2. rename or delete log file
3. attach db
SQL Server gives a warning but generates a nice new log file. Just make sure, you have a most current backup of that db and don't make this your usual procedere.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is the good old procedure from SQL 7.0. It works great.
create proc sp_force_shrink_log
/*
*************************************************************
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
Output Params:
--------------
Return:
Results:
---------
Locals:
--------
@err Holds error value
Modifications:
--------------
*************************************************************
*/
@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(25)
)
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
-
This worked for me many times.
dbcc shrinkfile (2, notruncate)
dbcc shrinkfile (2, truncateonly)
create table t1 (char1 char(4000))
go
declare @i int
select @i=0
while(@i < 100)
begin
insert into t1 values ('a')
select @i = @i +1
end
truncate table t1
backup log <db_Name> with truncate_only
drop table t1
go
-
What I have found to be a quick fix would be to run a backup of the transaction log, do a full backup of the DB. This can be done in Ent. Mgr.
Once that is complete, from query analyzer...
dbcc shrinkfile (filename_data, 500)
The filename_log is the name of the log file (which you can find by checking the properties of the Database in Ent. Mgr.) The 500 is a size to shrink to.
Once you have successfully run that you will need to do a transaction log backup and another full backup to clear the space. Be sure to overwrite and not append or your backup can be huge.
You may have to do this entire procedure a couple of times to get the size down.
I hope this helps!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply