Unable to Shrink Physical Size of Log | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Unable to Shrink Physical Size of Log


Due to some recent data conversion I have a huge LDF – ~20GB. I’ve tried all the actions I know of to reduce the physical size of it… Backup up the DB (set to FULL Recovery), Backed up the transaction log, used DBCC ShrinkDatabase & DBCC ShrinkFile… The first time I ran the DBCC ShrinkFile the log went down by about 5 GB — but remains at 15GB. I used the Shrink with the EMPTYFILE command but this did not affect the size. I’ve added another log file thinking that I could replace the existing one but the 15GB log is the PRIMARY and can’t be deleted. does someone have any suggestions? Many Thanks,
EKL
See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=671
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=508 HTH. Gaurav
Detaching and deleting the log worked. <br />Thank you so much for your help!! Very grateful and sooooo happy.<br /><br />EKL<br /><br /> <br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by eklpublic</i><br /><br /><br />Due to some recent data conversion I have a huge LDF – ~20GB. I’ve tried all the actions I know of to reduce the physical size of it… Backup up the DB (set to FULL Recovery), Backed up the transaction log, used DBCC ShrinkDatabase & DBCC ShrinkFile… <br /> <br />The first time I ran the DBCC ShrinkFile the log went down by about 5 GB — but remains at 15GB. I used the Shrink with the EMPTYFILE command but this did not affect the size. I’ve added another log file thinking that I could replace the existing one but the 15GB log is the PRIMARY and can’t be deleted. <br /> <br />does someone have any suggestions?<br /> <br />Many Thanks,<br />EKL<br /> <br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
DUMP TRANS database_name WITH NO_LOG
mj_mcpheat, this command will reduce the size of the active transaction log as a result of which the %age log used will decrease but it won’t reduce the physical size of the log. Gaurav
…. for that you should use DBCC SHRINKFILE and follow Gaurau’s referred links. _________
Satya SKJ
]]>