0

Here is my query in SQL Server 2005 that I use for truncating log files.

My log file will grow to 7G and when I execute this query in a nightly job it will truncate it to 10M. How do I replicate this query in SQL Server 2008 R2? My current query errors with NO_LOG.

Use Master

BACKUP log DBNAME WITH NO_LOG

--Shrinks the size of the Log (.ldf) file
USE DBNAME

DBCC SHRINKFILE (DBNAME_Log)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3498389
  • 123
  • 2
  • 3
  • 7
  • Why truncate it if it grows to that size every night? That's will mostly be reserved (i.e. empty) space once the transactions are committed and the process only has to grow the log again which is time consuming so just leave it. It won't grow 7GB per night (and if it does then you have something seriously wrong with your process) and you process will run a little quicker because it doesn't have to keep going and asking for more disk space. – Steve Pettifer Apr 30 '14 at 14:47
  • backing up the database should also clear the transactions in the log, though as Steve said, the already-reserved space will remain. – ps2goat Apr 30 '14 at 14:49
  • Steve, you recommend leaving it at 7G? My concern is it continuing to grow and maxing out the drive disk space. – user3498389 Apr 30 '14 at 15:15
  • 1
    You're not understanding what backup does. It backs up the data, marks the regions in the data file as free, and doesn't shrink the file. There's no reason to shrink the file. It would just grow again. This way the server reserves the space it knows it needs between backup cycles, and doesn't cause disk fragmentation problems because of repeated grow-shrink cycles. Finally, since this is how you're working with transaction logs, you probably need to be using Simple recovery instead of Full. http://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx – Bacon Bits Apr 30 '14 at 15:28

0 Answers0