June 3, 2008 at 10:12 pm
Transaction is taking more space after truncating also.
The database is in Full recovery mode as we need both data and log backup for security purpose.
If i change to simple recovery mode i can get only data backup not the log backup in case of any database corrupt or deletion and we can't do point in time restore in case of db corrupt.
So pls tell me how to solve this transaction log growth issue.
Pls reply asap.
June 3, 2008 at 11:13 pm
>> Transaction is taking more space after truncating also.
What is meant by taking more space? Does it mean log file size is same as before?
Did you shrink the log file?
Check the log space used using the following command.
DBCC SQLPERF(LOGSPACE)
Anyway after truncating log take full database backup.
June 4, 2008 at 12:33 am
Suresh,
Once we shrink it will reduce,but after some hours log file size increases about more than 2gb.Thats the issue.
I am shrinking daily.Wats the solution.
June 4, 2008 at 12:40 am
>>after some hours log file size increases about more than 2gb. Thats the issue.
May be, there are so many transactions. Check it with Profiler.
Don't shrink the log file, unless you have shortage of free space.
What is the data and log file size?
June 4, 2008 at 12:46 am
Data size is 8 gb and log size is 14 gb.
i checked through profiler lot of transaction are going on.
So during weekend the log file size reaches 20 gb
June 4, 2008 at 3:01 am
Hi,
Your transaction log is growing to accommodate the large number of transactions, as it's supposed to. Frequent shrinking will cause fragmentation and as the log file grows again it will use more server resources to do so - both not good. To control the size of the log, schedule more frequent transaction log backups. Log backups free up space within the log for reuse so if you monitor this you should be able to settle on the optimum size for the log and frequency of log backups. Check out 'Using Transaction Log Backups ' in BOL.
Cheers,
Cath
June 4, 2008 at 3:26 am
Cath,
Log file size was 5.62 gb.
I took transaction log backup twice but still the size is same when i crosscheck the size is still the same as 5.62 gb.
I think the log backup idea is working will monitor and let u know after some time.
Any other techniques?
June 4, 2008 at 5:36 am
Hi,
Yes, the log backup will only free up the space so it can be reused, so you won't see the size of the file decrease. If you want to know how much space is actually used inside the file try DBCC SQLPERF (LOGSPACE). That will give you the size of the log file in MB and the percentage actually used. From that you can work out how much free space there is. Monitor that for a while and you should be able to calculate a suitable log size and backup frequency.
Cheers,
Cath
June 4, 2008 at 6:04 am
Thanks cath.
I will monitor and will tell u.
June 4, 2008 at 7:03 am
I'd do exactly as Cath has suggested but just from your description of the problem (grows to 20GB over the weekend), I can tell you are not backing up the log frequently enough for the load you are having. If it's important to keep the log file at a certain size, increase your frequency of backups. And in reality, you kind of solved you own problem:
'I took transaction log backup twice but still the size is same when i crosscheck the size is still the same as 5.62 gb.'
It didn't increase in size because you did backups and freed the space in the log for reuse!!
-- You can't be late until you show up.
June 4, 2008 at 7:29 am
I agree with Cath and Terry. I do the same. Foe more clarification why you can not change the scheduled the Tlog job for every in 2 hours so.
It will free up the log space from the DB. Just to be make sure what is the autogrowth option you have been selected? Check the transaction is profiler or check with the DBCC opentran, It might be some transactions still remains open and contineous going on.
MCP, MCTS (GDBA/EDA)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply