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.
Please refer following link to get answer to your question :
To get the size of each open transactions refer following link
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.