Does the shrink data and log file command need to be done during maintenance hours or less busy hours?



  • Our data and log files have increased drastically due to a recent large INSERT query and DELETE query. The database in simple recovery mode.

    Does the shrink data and log file command take the database offline?

    I want to understand this to schedule whether the shrink on data and log file can be done during working hours?



  • Neither shrinking the database nor logs takes the db offline. See the https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15 :

    Other users can work in the database during file shrinking - the database doesn't have to be in single-user mode. You don't have to run the instance of SQL Server in single-user mode to shrink the system databases.

    That being said, https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ that shrink command, unless you really know it is needed. Shrink causes https://www.brentozar.com/archive/2015/04/dbcc-shrinkfile-blocking-locks-in-sql-server/ of blocking, so if it ever needs to be done, non-business hours are recommended. For an actual business case for shrinking see this https://am2.co/2016/04/shrink-database-4-easy-steps/ about how to do proper a shrink.

    Re-growing the files takes time. Even when https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15 is enabled, a transaction log growth will not use it. To make sure logs are readable after a crash, those are initially written full of zeroes, and that IFI cannot help with. So, if you shrink the db, then do again the operation that grows it, you are paying the IO price multiple times.

    For a runaway query that has enlarged the transaction log accidentally - or if the autogrowth settings are poor - consider making the log reasonably-sized by using MS TigerTeam's https://github.com/microsoft/tigertoolbox/tree/master/Fixing-VLFs .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2