|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Ever growing transaction log, using simple recovery?I've read a lot of posts on growing transaction logs. Most suggest that
setting it to Simple Recovery ensures that is automatically shrinks. Ours has been running Simple from the start. It's a database that is mostly read-only, some tables are filled using replication. Our production database is currently 160Gb, with an ever growing transactionlog which has now grown to 130Gb. The disks can handle the space requirement, but we're facing an ever growing increase in backup time, and heaven forbid, restore time. Whatever we tried, short of a suggestion of detaching the database and reattaching, nothing seems to help. We're running this on SQL 2005 SP1. Any ideas, suggestions? René renedevr***@gmail.com wrote:
Show quote > I've read a lot of posts on growing transaction logs. Most suggest that Simple recovery mode does NOT guarantee that the transaction log will > setting it to Simple Recovery ensures that is automatically shrinks. > Ours has been running Simple from the start. It's a database that is > mostly read-only, some tables are filled using replication. > > Our production database is currently 160Gb, with an ever growing > transactionlog which has now grown to 130Gb. The disks can handle the > space requirement, but we're facing an ever growing increase in backup > time, and heaven forbid, restore time. > > Whatever we tried, short of a suggestion of detaching the database and > reattaching, nothing seems to help. > > We're running this on SQL 2005 SP1. > > Any ideas, suggestions? > > René > shrink. It simply "truncates" the log automatically, meaning committed transactions are removed from the transaction log. The space freed up by removing those transactions is NOT removed from the log, it is made available for re-use by future transactions. If you check the actual space used in your transaction log right now, you'll probably find that very little of that 130GB is being used. Even in Simple mode, the transaction log has to be large enough to handle each transaction that is performed against the database. If a transaction produces 100GB of logging activity, then the transaction log has to be 100GB in size. Once that transaction completes, it is flushed out, but the log remains at 100GB. You have something running that is causing the log to be 130GB - I would suspect a large import process, or perhaps a reindexing job. Let me elaborate on this a bit.
>> committed transactions are removed from the transaction log << Not quite. Nothing is ever 'removed' from the log, unless you shrink it. Sections of the log (called virtual log files or VLFs) are marked for re-use, but only when all transactions with log records in those VLFs have been committed. Individual transactions do not have their log records removed, or anything like that. >> the transaction log has to be large enough to handle each transaction The log has to be big enough to hold your biggest transaction, plus all >> that is performed against the database.<< other transactions that ran concurrently with your longest transaction. Example: assume someone starts a transactions at 10 AM. It's either a very long transaction, or someone just forgets to issue the COMMIT, and it is the oldest open transaction. The VLF that contains the beginning of this transaction is considered active, and only VLFs that come before that VLF can be marked for reuse. After 10 AM, hundreds, maybe thousands of other transactions have started, processed their data, and been committed. But none of those can be cleared from the log, because the oldest open transaction started before any of them. So the log might need to be much much bigger than the open transaction, because it has to hold everything that cannot yet be reused. VLFs that come before the oldest active VLF can be marked for reuse when the log is backed up or truncated. To the OP: My guess is that you have an open transaction. Look in sysprocesses in the column called open_tran. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44ECBB37.5060406@realsqlguy.com... > renedevr***@gmail.com wrote: >> I've read a lot of posts on growing transaction logs. Most suggest that >> setting it to Simple Recovery ensures that is automatically shrinks. >> Ours has been running Simple from the start. It's a database that is >> mostly read-only, some tables are filled using replication. >> >> Our production database is currently 160Gb, with an ever growing >> transactionlog which has now grown to 130Gb. The disks can handle the >> space requirement, but we're facing an ever growing increase in backup >> time, and heaven forbid, restore time. >> >> Whatever we tried, short of a suggestion of detaching the database and >> reattaching, nothing seems to help. >> >> We're running this on SQL 2005 SP1. >> >> Any ideas, suggestions? >> >> René >> > > Simple recovery mode does NOT guarantee that the transaction log will > shrink. It simply "truncates" the log automatically, meaning committed > transactions are removed from the transaction log. The space freed up by > removing those transactions is NOT removed from the log, it is made > available for re-use by future transactions. If you check the actual > space used in your transaction log right now, you'll probably find that > very little of that 130GB is being used. > > Even in Simple mode, the transaction log has to be large enough to handle > each transaction that is performed against the database. If a transaction > produces 100GB of logging activity, then the transaction log has to be > 100GB in size. Once that transaction completes, it is flushed out, but > the log remains at 100GB. > > You have something running that is causing the log to be 130GB - I would > suspect a large import process, or perhaps a reindexing job. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Hi Tracy,
Thanks for the feedback, you make a very good point. But what surprises me then, is that in our case it never scales back to a smaller size. There are no transactions running, tried restarting the server, just to be sure. DBCC OPENTRAN dbname shows no active transaction.... Looking with DBCC SQLPERF (LOGSPACE) it actually shows that 99% of the space is used... This is why I am confused, I thought I understood Simple... :) René ------------------------------------- Tracy McKibben schreef: Show quote > > Simple recovery mode does NOT guarantee that the transaction log will > shrink. It simply "truncates" the log automatically, meaning committed > transactions are removed from the transaction log. The space freed up > by removing those transactions is NOT removed from the log, it is made > available for re-use by future transactions. If you check the actual > space used in your transaction log right now, you'll probably find that > very little of that 130GB is being used. > > Even in Simple mode, the transaction log has to be large enough to > handle each transaction that is performed against the database. If a > transaction produces 100GB of logging activity, then the transaction log > has to be 100GB in size. Once that transaction completes, it is flushed > out, but the log remains at 100GB. > > You have something running that is causing the log to be 130GB - I would > suspect a large import process, or perhaps a reindexing job. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com >> Thanks for the feedback, you make a very good point. But what surprisesme then, is that in our case it never scales back to a smaller size. >> The file itself won't shrink on its own! This is like expecting your fridge to take up less space when you are running low on groceries. and the problem is that if you make the fridge smaller, you are in rough water when you buy more groceries and find that they no longer fit in your now smaller fridge. For similar reasons, you don't necessarily want to just shrink your log file, because you may have a transaction tomorrow that requires 129GB. And it is more expensive for SQL Server to have to expand that file than to just re-use space in a relatively 'empty' file. Please have a good look at these articles: http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html http://www.karaszi.com/SQLServer/info_dont_shrink.asp A Aaron Bertrand [SQL Server MVP] schreef:
> problem is that if you make the fridge smaller, you are in rough water when Interesting comparison! I've read those articles, and I am not trying> you buy more groceries and find that they no longer fit in your now smaller > fridge. For similar reasons, you don't necessarily want to just shrink your > log file, because you may have a transaction tomorrow that requires 129GB. to be stubborn here.... But after I've removed all the groceries from the fridge, should I then not see that there is excess space in my fridge? In other words, should DBCC SQLPERF (LOGSPACE) not show me that at some point the Log Spaces User (%) lowers? I appreciate the point that it grows, and will not shrink the actual file to accomodate it for future use, and what has caused the log file to grow to begin with, can indeed happen again. That's fine. However, for the past two weeks this logfile has been growing consistenly by 8 Gb per day, and utilization is always between 99 and 100% René >> But after I've removed all the groceries from the fridge, should I thennot see that there is excess space in my fridge? In other words, should DBCC SQLPERF (LOGSPACE) not show me that at some point the Log Spaces User (%) lowers? >> As Kalen explained, they don't get deleted, they get marked for re-use.A > But after I've removed all the groceries from the fridge, should I then I think I found something to look into. Perhaps this pointer may help> not see that there is excess space in my fridge? In other words, should > DBCC SQLPERF (LOGSPACE) not show me that at some point the Log Spaces > User (%) lowers? > > I appreciate the point that it grows, and will not shrink the actual > file to accomodate it for future use, and what has caused the log file > to grow to begin with, can indeed happen again. That's fine. However, > for the past two weeks this logfile has been growing consistenly by 8 > Gb per day, and utilization is always between 99 and 100% > someone at a later stage. In this knowledge bage article: http://support.microsoft.com/?id=317375 I found some interesing info. One was the suggestion to look in sys.database why log reuse wasn't happening. THis is the command being used: SELECT [name], [log_reuse_wait], [log_reuse_wait_desc] FROM sys.databases In my cast, the result was: name log_reuse_wait log_reuse_wait_desc dbname 6 REPLICATION Obviously, something in the replication setup is failing. No idea what that might be, but al least it is something... Yes, a transaction marked for replication that has not yet replicated is
considered the same as an uncommitted transaction, and marks a point in the log after which no VLFs can be marked for reuse. -- Show quoteHTH Kalen Delaney, SQL Server MVP <renedevr***@gmail.com> wrote in message news:1156370617.358836.306890@74g2000cwt.googlegroups.com... >> But after I've removed all the groceries from the fridge, should I then >> not see that there is excess space in my fridge? In other words, should >> DBCC SQLPERF (LOGSPACE) not show me that at some point the Log Spaces >> User (%) lowers? >> >> I appreciate the point that it grows, and will not shrink the actual >> file to accomodate it for future use, and what has caused the log file >> to grow to begin with, can indeed happen again. That's fine. However, >> for the past two weeks this logfile has been growing consistenly by 8 >> Gb per day, and utilization is always between 99 and 100% >> > > I think I found something to look into. Perhaps this pointer may help > someone at a later stage. In this knowledge bage article: > http://support.microsoft.com/?id=317375 I found some interesing info. > One was the suggestion to look in sys.database why log reuse wasn't > happening. THis is the command being used: > > SELECT [name], [log_reuse_wait], [log_reuse_wait_desc] > FROM sys.databases > > In my cast, the result was: > name log_reuse_wait log_reuse_wait_desc > dbname 6 REPLICATION > > Obviously, something in the replication setup is failing. No idea what > that might be, but al least it is something... > renedevr***@gmail.com wrote:
Show quote >> But after I've removed all the groceries from the fridge, should I then Ahh, sorry, I didn't think about replication since you're using Simple >> not see that there is excess space in my fridge? In other words, should >> DBCC SQLPERF (LOGSPACE) not show me that at some point the Log Spaces >> User (%) lowers? >> >> I appreciate the point that it grows, and will not shrink the actual >> file to accomodate it for future use, and what has caused the log file >> to grow to begin with, can indeed happen again. That's fine. However, >> for the past two weeks this logfile has been growing consistenly by 8 >> Gb per day, and utilization is always between 99 and 100% >> > > I think I found something to look into. Perhaps this pointer may help > someone at a later stage. In this knowledge bage article: > http://support.microsoft.com/?id=317375 I found some interesing info. > One was the suggestion to look in sys.database why log reuse wasn't > happening. THis is the command being used: > > SELECT [name], [log_reuse_wait], [log_reuse_wait_desc] > FROM sys.databases > > In my cast, the result was: > name log_reuse_wait log_reuse_wait_desc > dbname 6 REPLICATION > > Obviously, something in the replication setup is failing. No idea what > that might be, but al least it is something... > mode. As Kalen stated, when replication is involved, committed transactions remain in the log until they have been pushed off to the distributor. They must remain in the log so that the logreader process can see them. After that, the normal truncation rules apply. Thanks, all. Another day, another insight. It get's weirder. It still
is stuck on the REPLICATION status, even after I removed all replication from it (and restarted SQL Server). And the log file keeps growing, we're now up to 140Gb, utilization 100%. The log contains 63 VLN all with status 0... I fear I have ended up in some unknow mode. Tonight we'll run a DBCC CHECKDB, to see if that clears up anything. Someone else suggested setting the mode to FULL, and perform a log backup. When done, we could then set it back to SIMPLE. The goal of this would be to get out of this status. BTW, for future reference; this is a good write up as well: Factors That Keep Log Records Active at http://msdn2.microsoft.com/en-us/library/ms345414.aspx In the meanwhile, more advice an insight is very welcome, I'll keep everyone posted on the progress. Show quote > > Ahh, sorry, I didn't think about replication since you're using Simple > mode. As Kalen stated, when replication is involved, committed > transactions remain in the log until they have been pushed off to the > distributor. They must remain in the log so that the logreader process > can see them. After that, the normal truncation rules apply. > And the solution in this case is.....
EXEC sp_repldone @xactid = NULL , @xact_segno = NULL , @numtrans = 0 , @time = 0 , @reset = 1 Read BOL for the gory details on sp_repldone, and why you need to be carefull with this. BUT... my database was still under the impression that it was configured as a publisher (which it was not). I had to re-enable it as publisher, ran the above command, and all of a sudden I have a 150 Gb tlog, with 0,0002 % utilization. I did shrink it to something more reasonable. Perhaps this may save someone else some time later on. Show quote > Thanks, all. Another day, another insight. It get's weirder. It still > is stuck on the REPLICATION status, even after I removed all > replication from it (and restarted SQL Server). And the log file keeps > growing, we're now up to 140Gb, utilization 100%. The log contains 63 > VLN all with status 0... > > I fear I have ended up in some unknow mode. Tonight we'll run a DBCC > CHECKDB, to see if that clears up anything. Someone else suggested > setting the mode to FULL, and perform a log backup. When done, we could > then set it back to SIMPLE. The goal of this would be to get out of > this status. > > BTW, for future reference; this is a good write up as well: > Factors That Keep Log Records Active at > http://msdn2.microsoft.com/en-us/library/ms345414.aspx > > In the meanwhile, more advice an insight is very welcome, I'll keep > everyone posted on the progress. > > > > > Ahh, sorry, I didn't think about replication since you're using Simple > > mode. As Kalen stated, when replication is involved, committed > > transactions remain in the log until they have been pushed off to the > > distributor. They must remain in the log so that the logreader process > > can see them. After that, the normal truncation rules apply. > >  |
|||||||||||||||||||||||
Other interesting topics