asked
Ldf file not reduced even Run T-log hourly basis????
I have a one Database its Size as see below .MDF size 10 GB **.LDF size 92 GB** database is Full recovery model. We run Transaction log backup every hour basis. ALTER DATABASE db1 SET RECOVERY SIMPLE; -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (db1_log, 1); -- Reset the database recovery model. ALTER DATABASE db1 SET RECOVERY FULL; **But it did nt shrink/shrink but again with in One hour it grows as 90-100GB** Why??? what are the causes? how to reduce the size of db1? **Note:** DBCC SQLPERF(LOGSPACE) details: Database Name: db1 Log Size (MB): 95619.24 Log Space Used: (%)90
Look at the `log_reuse_wait_desc` column in `sys.databases` for your database. It will tell you why the log isn't recycling. [
http://msdn.microsoft.com/en-us/library/ms178534.aspx][1] **Follow Up** I wanted to pull some of this out of the comments so it doesn't get lost... Because the `log_reuse_wait_desc` value was `REPLICATION`, my first thought was the log reader agent. It turns out you're using snapshot replication so the log reader agent isn't in use. With snapshot replication, there is a connect issue reported where transaction log reuse can be blocked by a DDL change on a replicated table: [
http://connect.microsoft.com/SQLServer/feedback/details/674437/snapshot-replication-prevents-transaction-log-from-shrinking][2] The connect item has a link to an mdsn blog post with more detailed description that contains a workaround: [
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx][3] Do you know if you've changed any of the tables? You might try the workaround in the msdn blog post. [1]:
http://msdn.microsoft.com/en-us/library/ms178534.aspx [2]:
http://connect.microsoft.com/SQLServer/feedback/details/674437/snapshot-replication-prevents-transaction-log-from-shrinking [3]:
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx
open replication monitor to see what the problem is. if your transactions aren't making it to the distributor, the log can't be reused. There are some useful steps in Books Online (Look for "Transactional Replication and the Transaction Log") -
http://msdn.microsoft.com/en-us/library/ms345414(v=SQL.90).aspx
The log reader is for transactional replication, so that won't be it. Snapshot replication had an issue where it would block transaction log reuse if you changed the DDL for one of the replicated tables. Have you changed any of the tables? Here is the connect item:
http://connect.microsoft.com/SQLServer/feedback/details/674437/snapshot-replication-prevents-transaction-log-from-shrinking And a slightly more detailed description with a workaround:
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx
Check your replicated environment - something isn't happening properly. Are your replication publishers / distributers and agents all working? Are all the targets all in sync?
You will need to look at what transactions are happening on the server. Something is churning through the system (long running transaction) that is keeping the VLF's in use. By chance do you have an index maintenance job that is continually running or batch process? Backing up the log can only free up (truncate) the VLF's that do not have open transactions in them. With this type of activity how many VLF's do you have dbcc loginfo (). You may need to consider cleaning those up as well. Kimberly Tripp has a great blog post on that you could read up on but basically you backup the log, shrink, backup the log, shrink and this triggers the logs to reset. Then set a proper auto growth size to manage the number of logs.
If it's regrowing to about the same size after an hour, then you have that many transactions going on and it needs to be that size. What about running the log backup every half hour or every fifteen minutes. Also, if you set the recovery to simple and then back to full, you need to immediately run a full backup otherwise, you won't be able to restore from the logs past the point when you set the database to simple recovery.
From the comments with @KenJ you have replication holding open transactions. That's going to prevent any changes if it's true. If it's not true, your workload needs a certain amount of space to hold the transactions that it has. That's how much space you're going to have to maintain over a given period of time. Assuming part of what you said was true, that the log regrew to 92gb, then that's how much room is needed to support one hour's worth of transactions. The only way to reduce that is to reduce the number and size of your transactions or increase the frequency with which you take backups. All that assumes that you don't just have open transactions, which might be the case here.
But backing up the log doesn't change the size. It just empties out the committed transactions.
16 People are following this question.
