Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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