Sql Server LDF file taking too large space

Question

Why is my database log file taking to high space? Its almost taking up 30GB of my HDD. Even after deleting 1,000,000 records, its not freeing up any space.
So,
1.Why is the log file taking this much space (30gb)?
2.how can I free up the space?

https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/ — Hadi, Mar 01 '19 at 12:43

score 13 · Answer 1 · answered Mar 01 '19 at 14:24

13

1. Why is the log file taking this much space (30gb)?

It was because the Autogrowth / Maxsize was set 200,000 MB

2. how can I free up the space?

As described Here i used the following command and the file is now less than 200mb

ALTER DATABASE myDatabaseName
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (myDatabaseName_log, 1)
GO
ALTER DATABASE myDatabaseName_log
SET RECOVERY FULL

I have also set Autogrowh/Maxsize in the database properties to 1000 as Limited (See the image below).

The link describes more, so I recommend referring it for detailed description and other options.
Thanks @hadi for the link.

answered Mar 01 '19 at 14:24

Qwerty

578
1
5
31

your database will be non-operational after the transaction log file reaches 1000 MB. And because you set it to **FULL** recovery, it will happen quite soon ;) – Alexander Volok Mar 01 '19 at 14:41
why? and why sql server provides that option if so? – Qwerty Mar 01 '19 at 23:36
because you enabled FULL recovery but perhaps do not understand when it should be used and what side effects it can bring. In very short: if you have full recovery enabled and do not perform transaction log backups, your log will keep growing and when it will the limit you set recently, your database will not be able to execute any DML operation, like INSERT/DELETE etc. I think this is a good article to explain in more details: https://www.mssqltips.com/sqlservertip/5343/understanding-sql-server-recovery-models-and-transaction-log-use/ – Alexander Volok Mar 03 '19 at 09:32

Alexander Volok · Accepted Answer · 2019-03-01T14:04:54.660

12

1.Why is the log file taking this much space (30gb)?

Or because of your recovery not SIMPLE and ldf grown eventually to such size
Or because there was a large one-time DML operation
Or because of other reasons, as noted by @sepupic in another answer

2.how can I free up the space?

IF recovery is other than SIMPLE:
- Firstly backup transaction log file
- Perform a shrink, like DBCC SHRINKFILE(2,256)
IF recovery is SIMPLE:
- Just shrink it to desired size, like DBCC SHRINKFILE(2,256)

If the database log still did not reduce to a target size, then the exact reason to be checked, by using a code snippet of @sepupic

Some members still give and advice to physicaly remove LDF files.

I highly suggest to not do this. Remarkable related post of Aaron Bertrand:

Some things you don't want to do:

Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

edited Mar 01 '19 at 14:04

answered Mar 01 '19 at 13:00

Alexander Volok

5,630
3
17
33

For me DBCC SHRINKFILE not reduce log file ldf (Recovery is SIMPLE). For me log_reuse_wait_desc not returns any data. DBCC SQLPerf(logspace) return 99,99% Log Space Used DBCC LOGINFO returns 11059 rows, all Status = 2. How get large one-time DML operations ? – Kiquenet Jan 28 '21 at 20:15
@Kiquenet, it worth to check what exactly prevents shrinking by: `select name, log_reuse_wait_desc from sys.databases where name = 'yourDB'` – Alexander Volok Jan 29 '21 at 10:49
For me log_reuse_wait_desc **not returns any data** – Kiquenet Jan 29 '21 at 17:40

score 5 · Answer 3 · answered Mar 01 '19 at 13:22

Why is my database log file taking to high space?

There can be more causes, not only the 2 mentioned in another answer.

You can find the exact reason using this query:

select log_reuse_wait_desc
from sys.databases
where name = 'myDB';

Here is a link to the BOL article that describes all the possible causes under log_reuse_wait: sys.databases (Transact-SQL)

how can I free up the space?

First you should determine the cause using the query above, then you should fix it, for example, if it's broken replication you should remove it or fix it.

score 1 · Answer 4 · answered Mar 01 '19 at 13:22

1

You need a maintenance job to backup the transaction log, and do it often: like every 10 minutes or so.

A FULL backup once per day isn't good enough.

Alternatively, you can change the Recovery Model of the database from FULL to SIMPLE. But if you do this, you'll lose the ability to do point-in-time restores.

answered Mar 01 '19 at 13:22

Joel Coehoorn

399,467
113
570
794

Just want to mention that log can grow to large size even in SIMPLE recovery by running some large DML operation. As an example - TEMPDB, it cannot have other recovery than simple but out of space of its log file can be a reason of a server downtime.. – Alexander Volok Mar 01 '19 at 13:51

score 0 · Answer 5 · answered Jan 14 '23 at 12:46

0

In my case the DB names was with bad characters so the script doesn't worked.

I found out and follow this article, which worked perfect in two steps: changing backup log from full to simple and shrink DB log file more than 95%

answered Jan 14 '23 at 12:46

ppenchev

127
1
10

Sql Server LDF file taking too large space

5 Answers5