unable to shrink log file

Question

unable to shrink log file

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

steveb. (7/24/2008)
-Take a full backup
-Change database to Simple logging
-Shirnk Log
-Change database back to full logging
Be careful though as Log files grow to a size that they need to, at more than likely will grow back to the same size.

I'll also add to anyone who may be reading this thread... I have to say "absolutely not". Changing the database to SIMPLE recovery breaks the backup chain. Even if replication weren't involved, you have to understand the ramifications of changing to the SIMPLE recovery mode. It's a far better thing to have some proper maintenance plans in place to backup the log on a regular basis so it doesn't get out of hand to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

matthew.peters (11/21/2010)
To anyone still browsing this forum. A way around shrinking a log file which wont shrink is by creating a new one and deleting the old one through moving the database files.
...
ALTER DATABASE AdventureWorks SET OFFLINE
and then move the .mdf file to the new location, then bring the database back online:
ALTER DATABASE database_name SET ONLINE
A new log file will be created

To anyone still reading (and not running screaming), do not ever do that. The log is not an optional piece of the database, SQL cannot always recreate it if it is deleted. Deleting the transaction log can result in the database coming up RECOVERY_PENDING and unusable.

http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55539 More actions · Answer 3

GilaMonster (11/21/2010)
To anyone still reading (and not running screaming), do not ever do that. The log is not an optional piece of the database, SQL cannot always recreate it if it is deleted. Deleting the transaction log can result in the database coming up RECOVERY_PENDING and unusable.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Does this also apply if the database was cleanly detached first, then the .ldf was deleted ?

I wouldn't intentionally delete it, but we were discussing moving databases across servers and various scenarios and the topic of a missing .ldf came up.

matthew.peters Ten Centuries Points: 1138 More actions · Answer 4

I can see criticism without an alternative solution??

To anyone still reading this (that is not kicking and screaming and has some common sense) this is offered as a solution to a growing log file which WON'T shrink.

You would implement the proper steps to move the database (as outlined in the original post), after the database was back online and possibly a full backup was taken you could then delete the log file. This has worked for me many times in the past and will work many times in the future. I hope people can use this