ldf in simple mode grew

Question

The next .ldf grew to 50GB, and is eating all the disk… (it is in SIMPLE recovery model)

I wanted to be able to roughly answer these questions:

-what’s inside the .ldf? (can I say it is just temp tables?!) -which command or user caused this 50GB to fill up? -potential issues if I force to shrink the file to 10GB.

I do not want this information to blame anyone, but to educate ourselves on the usage.

Amazingly I get this result:

Have you read this already?: http://stackoverflow.com/questions/826789/what-is-the-ldf-file-in-sql-server — Tim Biegeleisen, Feb 16 '17 at 10:06
Yes, I know what .ldf is; this case is a 50GB filled up log, from a db with simple RM. When I ask what's inside, I wanted to literally retrieve the name of the temp tables that are inside; or know what is inside. — Chicago1988, Feb 16 '17 at 10:11
50GB sounds pretty massive to me. How large are the tables in your database? — Tim Biegeleisen, Feb 16 '17 at 10:12
As you said that "Column 'log_reuse_wait_desc' = LOG_BACKUP", then it's 100% certain it's not in simple recovery. — dean, Feb 16 '17 at 12:25
sys.database says that in fact is. I attached the screenshot — Chicago1988, Feb 16 '17 at 12:55
OK, my appologies. You probably bumped into this bug: https://support.microsoft.com/en-us/help/2830400. — dean, Feb 16 '17 at 13:07

score 0 · Answer 1 · answered Feb 16 '17 at 13:07

Check for uncommitted Transactions by running the below script:

SELECT 
er.session_id
,er.open_transaction_count
FROM sys.dm_exec_requests er

you can use sp_who2 to track down the user.

If you urgently need to free-up space in the Log file - without performing a shrink - take a log-backup.

ldf in simple mode grew

1 Answers1