0

Prologue

I have always read/observed that we should not shrink database file as they tend to grow back. There will be a performance penalty when DB will try to grow these files if there is not enough space already.

Situation

When I am executing following query for few of my databases -

select * from sys.dm_db_log_space_usage

Some of my databases are taking around 20 GB space. Catch is column used_log_space_in_percent is showing values between .1 to 10 %. If I shrink these databases I can actually gain around 100 GB space immediately. Also please note that LogReuseWaitDesc is "nothing" for some of DBs if not all.

Due to some reason, transaction log backups are not possible in near future. (Convincing in progress)

It will be really helpful if you can provide recommendations as well as reasoning in such cases whether its a good idea to shrink files or not.

If you are taking time to think about this, THANK YOU!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
UVData
  • 459
  • 2
  • 6
  • 13
  • One of the bigger reasons you shouldn't shrink databases is more than it *will* cause fragmentation, which is a huge problem for indexes. This mean you end up needed to rebuild you indexes, which means your database grows, and may still end up with fragmentation afterwards. You just end up chasing your tail and cause (possibly significant) performance losses. – Thom A Feb 17 '20 at 15:04
  • Do you know *why* your logs grew to those sizes at some point? – SchmitzIT Feb 17 '20 at 15:26
  • I understand that we should not shrink files but having 90% to 99% free space should not be acceptable. – UVData Feb 17 '20 at 16:17
  • I am still not sure and that investigation is WIP. Looks like someone/process executed some heavy queries. I have recently taken over this server and a lot needs to be fixed. But I want to understand this scenario and looking for best practises. – UVData Feb 17 '20 at 16:20
  • I don't understand why having log files at low percent is a bad thing? – Hogan Feb 17 '20 at 18:56
  • 1
    @Hogan - It won't "hurt" as such, but if I understand correctly, OP is hoping to reclaim some unused disk space. Which is a valid reason, provided the log-files didn't grow to this size due to "regular" traffic, as opposed to a 1-time bulk load or something like that. If the size is needed for regular operations, reducing the size of the logs will end in reduced performance at some point. In case of it being a 1-time thing, there shouldn't be any issues. – SchmitzIT Feb 18 '20 at 08:21

1 Answers1

0

ldf files grow if you don't get backup, as soon as you create backup, SQL Server truncates log file. Shrinking log files is not a good idea unless you have a backup. Please read the followings :

https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

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

Amin Pashna
  • 141
  • 1
  • 1
  • 6