Transaction Log Size

Question

Transaction Log Size

er sql

SSC Eights!

Points: 943
More actions
July 6, 2005 at 9:03 am

#110320

Hi, I've a problem with some Transaction logs in different databases.
I usually run a checkpoint and backup log "database" with truncate_only statement ro reduce transaction log size and then I ran shrink database command, but with some transaction logs it 's not working, after running all the statements the log size is the same size, why?, how can I reduce the transaction log?.

Thanks for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

What is happening is that the active part of the log is located near the end of the file. Therefore you cannot shrink the file since you can't truncate the active part of the log. What you need to do is empty the log and then move the active part of the log to the beggining. I don't do this anymore since I now use transactional log backups, and where those are not required I put the recovery mode in simple and set the log not to auto grow. Here is a copy of the procedure I used to use it is by far not the best way to do things, but this can demonstrate what the problem is and how to get around it.

--creation begins here.

CREATE proc sp_shrinklog

as

declare @dbname sysname, @JH int

set nocount on

-- db_name() is from the pick list

select @dbname = db_name()

-- issue checkpoint, forces dirty pages to be flushed to disk

checkpoint

/*NOTRUNCATE: freed file space to be retained in the files

dbcc shrinkfile (file_id,target_size,truncate-option)

GET THE FILE ID INFO:

--------------------------

select fileid,name from sysfiles where name LIKE 'YourDBName%'

*/

exec('DBCC SHRINKFILE (2, 1, NOTRUNCATE)')

--exec('DBCC SHRINKFILE (3, 1, NOTRUNCATE)')

--TRUNCATEONLY: any unused space in the files to be released to

-- the operating system and shrinks the file

exec('DBCC SHRINKFILE (2, 1, TRUNCATEONLY)')

--exec('DBCC SHRINKFILE (3, 1, TRUNCATEONLY)')

--waste some time....

exec ('create table tmp1 (char1 char(4000))')

select @JH = 0

while (@JH < 1000)

begin

exec('insert tmp1 values (''a'')')

select @JH = @JH + 1

end

--clean up

exec('drop table tmp1')

--shrink the logfile after

exec('BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY')

GO

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 2

Hi,

also i would recommend not to use BACKUP LOG WITH TRUNCATE_ONLY but BACKUP LOG WITH TRUNCATE, because if the database backup fails for some reason, after doing ..WITH TRUNCATE_ONLY you lose the ability to restore completely...

regards

karl

Best regards
karl