6

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which fail and do not seem to release the log space any more. So I would like to find out the statements and how much space it used in the transaction log during a specific period in time.

I'm thinking of something like using the output from fn_dblog(null,null) and aggregate the output accordingly. But that seems to be quite daunting as there is no official documentation.

Any suggestions and help welcome.

  • 2
    Have a look at this SO thread – Shanky Aug 17 '15 at 7:12
  • 1
    AFAIK there is no way to find how much log was generated by transaction which started sometime before and have now committed. The fact that the VLF's might have been reutalized make it even more tough. – Shanky Aug 17 '15 at 7:29
1

Please refer following link to get answer to your question :

To get the size of each open transactions refer following link

https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

If you want to know the transaction log of any historical date then you can take the backup of database or just a transaction log backup and use third party tools to read those logs. It will also allows you to read the the logs taken by date.(ApexSQL Log tool, SQL Log Rescue)

Hope this helps.

  • 1
    Article is saying use command DBCC OPENTRAN to get active transactions, so can we get space usage information against those transactions ? and how about if any transaction took huge space during his processing in night and when DBA came in morning he determined a heavy amount of space is occupied by log file, is there any way to find which transaction was culprit? as its no more in active transactions. – AA.SC Aug 17 '15 at 6:24
  • I have opened a Connect issue. Still have to look at suggested 3rd pary tools – dim Aug 23 '15 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.