April 19, 2011 at 7:30 am
Hi,
In one of our server, we are taking transaction log backup every 1 hr still the log file grows huge for a user database(full recovery model). I cannot see and long running or uncommitted transactions. Could you please guide me finding the root cause of this issue?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 7:33 am
How big is the log "normally" and how much has it grown to?
April 19, 2011 at 7:51 am
Have a read through this: http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
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
April 19, 2011 at 8:38 am
create checkpoint then take log backup and then truncate log files
CHECKPOINT;
BACKUP LOG [TEST] ......
GO
DBCC SHRINKFILE('Log file 1 name',TRUNCATEONLY);
DBCC SHRINKFILE('Log file 2 name',TRUNCATEONLY);
GO
take orignal and final backup some other place then apply this one will overwrite option
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 8:40 am
Hi,
The log has grown to 48Gb in one month where the database size is 43 GB.
I have checked log_reuse_wait_desc and it shows LOG_BACKUP
MSDN site says "A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
(Note
Log backups do not prevent truncation. )
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable."
Log back is taken every hour and no_truncate option is not included while taking the backup.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 8:43 am
Check that the log backups are succeeding.
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
April 19, 2011 at 8:50 am
Log backups are taken successfully every hour.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 8:53 am
Any big data load or reindexing?
April 19, 2011 at 8:57 am
I am not sure about the big data load since I have no information what the appliaction does.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 8:59 am
How often do you do your FULL backup? or when was the last time that FULL backup was run?
April 19, 2011 at 9:00 am
Sumanta Roy (4/19/2011)
I am not sure about the big data load since I have no information what the appliaction does.
Reindexing job? That's usually culprit #2 right after not taking successfull log backups.
April 19, 2011 at 9:51 am
Full backup is taken every week Sunday.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 9:53 am
Sumanta Roy (4/19/2011)
Full backup is taken every week Sunday.
REINDEX ??????????????????????????????????????????????????????????????????????
April 19, 2011 at 9:54 am
We do have one reindexing job based on the fragmentation which runs every week sunday. So every table will not be affected. Log backups are taken successfully.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 19, 2011 at 9:58 am
Fine, assuming the log backups are running fine.
Shrink the t-log a couple minutes before the reindexing. And see how big it grows back to after the job has run.
Even if the job is selective, it can still reindex a whole freaking lot. That's your best bet at the moment.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply