Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 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.
