CODEWITHSUNDEEP
sql-serversql-server-2012transaction-log
UVData
UVData

Reputation: 511

Very Low "Used Log Space Percentage" for SQL Server database transaction file

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!

Upvotes: 0

Views: 1766

Answers (1)

Amin Pashna
Amin Pashna

Reputation: 155

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/

Upvotes: 0

Related Questions