0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.