CODEWITHSUNDEEP
sqlsql-serverloggingsql-server-2005
Rauf
Rauf

Reputation: 12842

SQL Server Database Transaction Log File size increased dramatically

After a backup, I noticed size of SQL Server Database Transaction Log File increased dramatically. We were having 10 GB free space, now I could see only 9 MB.

Where should I check to find the root cause of this issue ?

Upvotes: 0

Views: 966

Answers (1)

MillhouseD
MillhouseD

Reputation: 171

The first thing I would check is if there is an open transaction in the database. A transaction log backup only truncates to the oldest open transaction.

I use Adam Machanic's sp_WhoIsActive with the @get_transaction_info = 1 switch. http://sqlblog.com/

you could also use:

SELECT  * FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_exec_requests er ON tat.transaction_id = er.transaction_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle);

DBCC OPENTRAN() does not always show me all the open transactions.

Upvotes: 1

Related Questions