TransWikia.com

Unable to shrink the transaction log file in Sql Server 2008

Server Fault Asked by user18576 on December 30, 2021

Am using the below to script shrink the transaction log

*USE AVPC
GO
BACKUP DATABASE AVPC
TO DISK = ‘H:DBBackupAVPCFullBackUpAVPC.bak’
USE AVPC
GO
BACKUP LOG AVPC
TO DISK = ‘H:DBBackupAVPCTransactionAVPC.trn’
GO

USE AVPC
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AVPC
SET RECOVERY SIMPLE;
GO
checkpoint
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AVPC_log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE AVPC
SET RECOVERY FULL;
GO*

But I was not able to succeed, I didnt get any error but the log didnt shrink.I also verified for any open transactions,but there was none.
And also we do Full Back for twice a week and transaction log back up for every mins.

Can any one help me to shrink the log file.It is really growing big.

2 Answers

It also depends on sizes of your Virtual Log Files inside of your log If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb

Answered by Oleg Dok on December 30, 2021

DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?

Answered by jl. on December 30, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP