asked
How to know What operations are happening from LDF fIle?
Hi We are using SQL 2008 R2. We have 24 Databases. We are implemented Transaction log backup Every one hour and Daily Database Backup Daily. But We noticed that one Database LDF is growing very large. kindly find the attachment for LDF Backup every one hour details(File size More than 1 GB) ![alt text][1] My Question 1)What are the Steps/Action to stop LDF file Growing very big even enabled Transaction log backup Every one hour? 2)How to know what operations(insert/update/delete) are happening in Database from LDF file of Every One Hour? Because If we identify What Operations are happening in every one hour then we can optimize on it. 3)How to stop growing LDF file of that Particular Database? [1]: /storage/temp/1568-tlog+ldf+growth.jpg
The LDF growth in size is going to reflect some sort of large transaction (or many transactions), thus causing the LDF to grow as well as your transaction log backups. 1) You can disable the auto growth of the log file, however I would caution (IE: I would **NOT** do this) you in doing this simply because if your log file hits the limit, your database (and all associated applications that might be using the database) will stop. Here a references:
https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/ 2) I would suggest that you look at using a server side trace or extended events in order to track down which transactions are causing the growth. If you're not familiar with extended events, you can check out Jonathan Kehayias's blog series on it.
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx Server side traces:
http://www.sqlservercentral.com/articles/Performance+Tuning/71549/ Adam Machanic has a very nice tool called 'sp_whoisactive' that is free for use and can be found here:
http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx. Another great way to see what might be going on in that hour. I would also look at any type of ETL loads or maintenance routines (index rebuilds?) as they can cause growth depending on what they are doing. 3) See answer #1. Once you set a maximum limit on the file size, the file will grow to that size and then stop. Again, I **DO NOT** recommend this approach in any way shape or form. Instead of limiting the file size, I would 1) Determine what transactions are causing the explosive log growth. Do you have any type of monitoring tools in place? I highly recommend SQL Sentry and/or Red Gate as an option. 2) If the backup sizes are a concerning (maybe from a recovery standpoint) you could look at decreasing the span of your transaction log backups so that the backup sizes are smaller, maybe every 30 minutes. You will however get more of them. Hope this helps!
15 People are following this question.
