May 28, 2008 at 10:06 am
Hi,
I have the database which has simple recovery option. The size of data file is 2 gb But the
log file is huge almost 9 GB .I shrink the file but it grow again up to 9 Gb.
Why the log file is huge ?;)
Best of Regards
Hammad khan
May 28, 2008 at 10:42 am
You either have an open transaction preventing it from shrinking or being cleared, or you are performing a large transaction that needs that space.
Log space is in no way related to data space.
May 28, 2008 at 11:10 am
some transactions like index rebuilds are fully logged regardless of what your DB is set to
May 28, 2008 at 11:24 am
Thanks a lot for the reply. I have the job which run every minute to update and delete .
when the job is running , it's opening the transaction and then closing it.
I monitor the DB and noticed that is always a open transaction whcich is preventing
the shrinking the log file.how can i shrink the log while there is always a open transaction?
May 28, 2008 at 11:44 am
The fact there there might be open transactions open at any given time isn't the problem. The problem would be if it's the SAME transaction that's never getting committed. The log will only truncate up to the latest nucommitted transaction, so if something is "stuck open" it can't truncate past it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 12:40 pm
Hammad khan (5/28/2008)
Thanks a lot for the reply. I have the job which run every minute to update and delete .when the job is running , it's opening the transaction and then closing it.
I monitor the DB and noticed that is always a open transaction whcich is preventing
the shrinking the log file.how can i shrink the log while there is always a open transaction?
Forget about trying to shrink the log file. As you stated earlier, you performed the shrink - but it just grew back to the 9GB size later. My recommendation is:
1) Leave the size of the log file as it is (actually, I would increase the size to 10,000MB)
2) Try to identify the process or processes that need that amount of log
a) Could be reindexing
b) Could be data loads
c) Something else?
3) Once you have identified what is using the log space, determine whether or not that process can be changed to reduce the amount of log space needed. If it can be modified and reduces the amount of log space needed, then - and only then, shrink the log file back to an 'acceptable' size.
At this point, the log file needs 9GB to handle all of the transactions and, if you shrink it again, will just grow back to 9GB again. By shrinking the log file all the time you are causing performance issues. The process that is causing the log file to grow has to wait for the file to grow (probably in very small increments also) before it can continue.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2008 at 11:03 am
Than you very much. I moved the databases from local drive to SAn and
reboot the server. I did it on Wednesday night and so far the Log file is not huge.
The log file is around 2 GB which is much better then 22 GB.:)
Regards
Hammad khan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply