January 23, 2004 at 6:52 am
My transaction log keeps filling up, even though it's backup nightly and should be getting truncated any time it's backed up. I ran dbcc opentran to see if there is any open transactions and I don't see any, can anybody what is causing the T-log to get so big, is there anything that I'm missing here.
Thanks
January 23, 2004 at 8:17 am
My first thought is that you have a reindex job running after the backup job...
January 23, 2004 at 8:21 am
why does the reindex job fill up the t_log?
January 23, 2004 at 8:34 am
My experience. 🙂
Check this link:
http://www.microsoft.com/sql/techinfo/tips/administration/logfilegrowth.asp
Also please search this forum, there are lots of discussions about this topic already.
Good luck!
May 18, 2009 at 12:04 pm
If there is no logshpping in place, Better to truncate the log after backup. Log will grow due to heavy DML operations on the tables/objects.
There may be a huge space between the data pages.
Use following,
----------------------------------------------------
Checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
backup log with truncate_only
----------------------------------------------------
May 18, 2009 at 1:31 pm
amina omar (1/23/2004)
why does the reindex job fill up the t_log?
The reindex operation is a fully logged operation, that is why it writes to the transaction log. If you need to recover to a point in time, the log needs to know what operations had occurred.
May 18, 2009 at 1:40 pm
amina omar (1/23/2004)
My transaction log keeps filling up, even though it's backup nightly and should be getting truncated any time it's backed up. I ran dbcc opentran to see if there is any open transactions and I don't see any, can anybody what is causing the T-log to get so big, is there anything that I'm missing here.
Thanks
Probably it needs that much space?
The tlog grows as you tell it, I mean you have complete control over it if you manage it properly.
Before we advise you anything you should have to let us know the type of transactions running against this database? What recovery model it is set to? You need to investigate when it is getting full and what are the operations that are run just before the Tlog is full.
May 18, 2009 at 1:42 pm
pradyothana (5/18/2009)
If there is no logshpping in place, Better to truncate the log after backup. Log will grow due to heavy DML operations on the tables/objects.
No. Do not truncate the log
There may be a huge space between the data pages.
Sorry, did not get it?
Nope, not at all please do not follow this.
dbcc freeproccache
dbcc dropcleanbuffers
These are irrelevant to tlog file growth.
backup log with truncate_only
Nope, do not. This will break the log chain.
May 18, 2009 at 1:51 pm
If you do not have a DR plan with Log shipping, check the mode for the database. If it is full make is simple. But I advice against it.
The best thing to do would be to do Transaction log back ups as well. If you are doing that, the transaction log wont grow that much. It does not hurt to keep the T Log big. You can leave it as it is unless you have space constrains.
-Roy
May 18, 2009 at 2:02 pm
pradyothana (5/18/2009)
If there is no logshpping in place, Better to truncate the log after backup.
That is terrible advice to give, especially since you don't mention what truncate only does and the effect on log backups.
If the DB is in full recovery and the log is being backed up to allow point-in-time recovery, then backing up the log with the truncate_only option is stupid. It breaks the log chain by discarding log records. No log backup can be made after that has run and point-in-time recovery is impossibel until another full backup is run.
Please read through this - Managing Transaction Logs[/url]
Bottom line, if point-in-time recovery is needed, then the DB should be in full (or maybe bulk-logged) recovery and log backups need to be taken. If point in time recovery is not required, then set the DB into simple recovery and forget about the log
dbcc freeproccache
dbcc dropcleanbuffers
With no mention of what those are going to do to a server?
FreeProcCache will force every single stored procedure and query to recompile on the next execution. That means significantly higher CPU usage for a while after running that statement. On a busy production server that's near guaranteed to result in slow performance
DropCleanBuffers discards all cached data pages from memory. That means that queries running after that have to go to disk to get data rather than getting it from memory. That means much greater IO load and much slower queries for a while. Again, on a busy production server that's near guaranteed to result in slow performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 2:18 pm
amina omar (1/23/2004)
My transaction log keeps filling up, even though it's backup nightly and should be getting truncated any time it's backed up.
Transaction log backups or database backups? Only tran log backups truncate a log. Full database backups do not.
Do you need the ability to restore to a point-in-time (in case of a DB failure) or is restoring to the last full backup acceptable?
I ran dbcc opentran to see if there is any open transactions and I don't see any, can anybody what is causing the T-log to get so big, is there anything that I'm missing here.
SELECT name, log_reuse_wait_descr FROM sys.databases
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 2:38 pm
Thanks Gail(GilaMonster/Gail Shaw) for updating Me
July 29, 2009 at 5:27 am
hi gail ,
i know this is not right way to ask the problem, so apology for this time
but
please see the below link's problem
http://qa.sqlservercentral.com/Forums/FindPost761384.aspx
thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 29, 2009 at 6:41 am
bhuvnesh.dogra (7/29/2009)
i know this is not right way to ask the problem, so apology for this time
If you know it's not the right way to ask, why are you doing it?
I look at all the threads here that I think I can answer and, if I can answer I answer them. If I don't post on your thread it means I'm busy, don't have anything to add, or don't know the answer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2009 at 6:52 am
i really apologise for this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply