HUGE Log File
-
Hey boys,
Quick question. Whats the quickest way to shrink a log file. I was recently handed a database in need of some maintenance. The first thing that I did was change the Recovery Model from Full to Simple since the log file has grown to 51Gb.
What is the quickest way to shrink this down to a "good" size?
Thanks for all the help
-
Really the quickest way?
Detach the db, rename or delete the log, attach the DB
or use
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE
Btw, changing the recovery mode has not much to do with the growth of the log file. In doing so, you give up your ability to restore to the point of failure or a point in time.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If it's full, use the no_log instead of trunate_only. I usually follow Franks advice when I need to shrink one.
-
Steve,
Aren't the TRUNCATE_ONLY and NO_LOG synonymous?
-
No. TRUNCATE_ONLY clears committed transactions and writes a record to the log. So if it's full it fails and then suggests that you use NO_LOG (which does the same thing minus the log record).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy,
You are throwing misleading information. NO_LOG and TRUNCATE_ONLY does the same thing in SQL Server 2000. There is no difference at all.
-
Piggy-backing on Nitin. That's right. Here's something interesting from BOL:
Expect different results as compared to earlier versions of SQL Server. Expect the NO_LOG and TRUNCATE_ONLY clauses of the BACKUP or DUMP statements to behave identically.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply