-1

Am using the below to script shrink the transaction log

*USE AVPC GO BACKUP DATABASE AVPC TO DISK = 'H:\DBBackup\AVPC\FullBackUp\AVPC.bak' USE AVPC GO BACKUP LOG AVPC TO DISK = 'H:\DBBackup\AVPC\Transaction\AVPC.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 2

0

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .