-5

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?

5
  • 1
    Before shrinking the files, are you sure you won't have another "large INSERT query and DELETE query" as you described?
    – Ronaldo
    May 13 at 11:16
  • Yes there was once a year type of activity conducted to delete archived data.
    – variable
    May 13 at 11:41
  • Please avoid shrinking files, except under extreme circumstances. Maybe an AG replica went offline for 2 days, and your transaction log grew by 8x its original size. Or maybe you just deleted 500GB of data from your database. But as is mentioned in vonPryz's answer, if it's expected the file will grow back after the shrink, you're just wasting server resources that could be better used for other tasks. May 13 at 12:06
  • Yes I understand, but given that this is like a one off thing do I need to schedule the data file shrink out of busy hours and the log file shrink can be done during normal business hours?
    – variable
    May 13 at 12:08
  • I would read this by Kendra Little. Do it in the smaller chunk. Sometimes you might have to do some tricks for this to work. Increase the file size by a few MB. Take care of fragmentation at the end. May 13 at 12:28

1 Answer 1

3

Neither shrinking the database nor logs takes the db offline. See the docs:

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, please do not run that shrink command, unless you really know it is needed. Shrink causes a lot of blocking, so if it ever needs to be done, non-business hours are recommended. For an actual business case for shrinking see this article about how to do proper a shrink.

Re-growing the files takes time. Even when instant file initialization 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 VLF solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.