Why is my LDF so large?

Question

I have a Microsoft SQL Database with an LDF of 500MB. Usually, this is less than 1mb. Recovery Model is set to simple. This is SQL Server 2014 Standard x64. Any idea why the LDF would be 500mb if the recovery model is simple? I have tried shrinking it. it caught my eye because it was 22gb.

This is a database for a Practice Management Software for Ortho. I have approximately 500 deployed and this is the only one that is larger than 1 MB when in simple recovery.

TheGameiswar · Accepted Answer · 2017-09-26T14:03:02.087

2

This is because Log file won't shrink automatically once it grows.. you can shrink manually(if you are facing storage issues) or leave it as it is..

I would recommend to leave it as it,since file operations are expensive

As per the chat we had ,we concluded the cause for log growth is a deferred transaction

edited Sep 26 '17 at 14:03

answered Sep 25 '17 at 16:11

TheGameiswar

27,855
8
56
94

I have tried shrinking it multiple times, it was 22gb. – Devon Quick Sep 25 '17 at 16:21
what does this show `select database_id,log_reuse_wait_desc from sys.databases` for that database – TheGameiswar Sep 25 '17 at 16:22
7 ACTIVE_TRANSACTION – Devon Quick Sep 25 '17 at 16:23
try running this `dbcc opentran` and see what is the transaction – TheGameiswar Sep 25 '17 at 16:24
No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator. – Devon Quick Sep 25 '17 at 16:25
what does this show `select * from sys.dm_tran_active_transactions where name='user_transaction'` – TheGameiswar Sep 25 '17 at 16:29
There are no results. – Devon Quick Sep 25 '17 at 16:30
1

Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155267/discussion-between-thegameiswar-and-devon-quick). – TheGameiswar Sep 25 '17 at 16:31
it was a deferred transaction. add the answer. @TheGameiswar – Devon Quick Sep 26 '17 at 13:41
@DevonQuick added answer – TheGameiswar Sep 26 '17 at 14:04

Why is my LDF so large?

1 Answers1