how to change the default shrink size of logfile?

Answers

• 

  

  0

  Sign in to vote

  Hi,

  If it's telling you the minimum it can shrink the log file to is 220MB, it means that there's activity in the log.  You can take a look at the log_reuse_wait_desc column in sys.databases to see what's stopping the log space from being reused.  Usually it's something like it's waiting on a LOG_BACKUP, Checkpoint or replication or something.

  Are you aware that shrinking files is generally considered bad practice and can seriously hurt performance - especially the log file?  You may want to read the following:

  http://www.karaszi.com/sqlserver/info_dont_shrink.asp

  http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

  http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

  ---

  
  Thanks, Andrew
  

  • Proposed as answer by Olaf HelperMVP Sunday, April 21, 2013 7:02 AM
  • Marked as answer by Maggie Luo Sunday, April 28, 2013 3:59 PM

  Friday, April 19, 2013 10:05 AM
• 

  

  0

  Sign in to vote

  To know about how much space your log files are consuming and how much is free to try shrink operation you can run the command:

  dbcc sqlperf(logspace)

  To know about shrinkfile options see this:

  http://msdn.microsoft.com/pt-br/library/ms189493.aspx

  Regards,

  ---

  André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

  • Proposed as answer by Olaf HelperMVP Sunday, April 21, 2013 7:03 AM
  • Marked as answer by Maggie Luo Sunday, April 28, 2013 3:59 PM

  Friday, April 19, 2013 12:06 PM