-1

Can I shrink transaction log when database in online using below command ? DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

FYI my database is in production server & highly critical

Thanks in advance

regards Imra

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43
imra
  • 1
  • 1
    Yes you can shrinking only removes free space but I would not advise you to do unless you tell me reason why you want to shrink – Shanky Feb 10 '15 at 09:19
  • 1
    "production server & highly critical": Don't shrink! – Colin 't Hart Feb 10 '15 at 10:29
  • So what are you going to get out of shrinking? In a production system, the log file will grow again... shrinking and growing is like emptying the vacuum cleaner out onto the carpet. Sure, the vacuum cleaner is clean (temporarily) but now you have a mess to clean up. Repeat for infinity... – Aaron Bertrand Feb 10 '15 at 14:58

1 Answers1

0

You could, but that doesn't mean you should.

Your goal, presumably, is to reduce the amount of space being used by your transaction logs because they're large, and the database is in the 'Full' recovery model.

However, the transaction log has grown this big for a reason. Have you dealt with this this first? If you don't, then it will grow again. Perhaps growing to the size it has isn't a problem, but normal operation?

If you have considered these factors, then you need to do a log backup to truncate the entries in the log file, and only then shrink the file. Be aware that shrinking a datafile is a highly I/O intensive operation and WILL have a performance impact on your system.

See https://msdn.microsoft.com/en-GB/library/ms190925.aspx#Truncation for info on backing up your transaction log and https://msdn.microsoft.com/en-GB/library/ms365418.aspx#ShrinkSize for details on shrinking.

Phil Sumner
  • 1,867
  • 11
  • 13