Which command to reduce log fle ?

• contact 14920

  Right there with Babe

  Points: 725

  More actions

  May 23, 2017 at 4:29 am

  #1943732

  prettsons - Tuesday, May 23, 2017 2:42 AM

  Read about DBCC SQLPERF form here: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql

  Thank you 🙂

• contact 14920

  Right there with Babe

  Points: 725

  More actions

  May 23, 2017 at 4:34 am

  #1943733

  GilaMonster - Tuesday, May 23, 2017 2:06 AM

  contact 14920 - Tuesday, May 23, 2017 1:49 AM

  Hi,

  It's a little database : 
  one file *.mdf : 8 Gb
  one file *.ldf : 15 Gb
  Critical : yes because health database so not recommanded to stop production : you're right
  So if I would shrink file, best method is method 3 ?
  I have understand that it's not a good practice to shrink log file but 15Gb for a file who has only 0.5% used by transaction....
  Thank you
  anthony

  If the log file is growing, then it needs to be that space, you can either take more frequent log backups or accept that the log needs to be larger, size if for the transactions and leave it alone.

  If the file is not growing, then it may be acceptable to shrink it. You'll need to first identify how large it needs to be (run DBCC SQLPERF(LOGSPACE) just before each log backup to get the max percentage that the log actually uses of the file.
  If you do decide to shrink it (AFTER such research, not as a knee-jerk reaction), you don't need simple recovery or single user mode. It's an online operation, just do it at a quiet time.

  Did you get the book mentioned earlier in this thread?

  Hi Guru,

  Command : dbcc sqlperf(logspace)
  Result : Log Size : 14974.55 / Log Space Used (%) :0.3 %
  I'm going to read book

  Thank you Guru for your reply

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 23, 2017 at 4:49 am

  #1943734

  contact 14920 - Tuesday, May 23, 2017 4:34 AM

  Hi Guru,

  Command : dbcc sqlperf(logspace)
  Result : Log Size : 14974.55 / Log Space Used (%) :0.3 %

  run DBCC SQLPERF(LOGSPACE) just before each log backup

  Not once. Before each log backup, for at least a day.

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 23, 2017 at 4:49 am

  #1943735

  contact 14920 - Tuesday, May 23, 2017 4:21 AM

  John Mitchell-245523 - Tuesday, May 23, 2017 1:59 AM

  What do you get if you run this?

  SELECT MAX(backup_size)
FROM msdb..backupset
WHERE database_name = 'toto'
AND backup_finish_date > CURRENT_TIMESTAMP - 14
AND type = 'L'

  John

  Result : 7345895424

  That means your largest log backup in the last two weeks was 7GB in size. Therefore don't shrink your log any smaller than that, unless you're absolutely certain that the 7GB log backup was caused by a one-off event.

  John