Log File Improper growth
-
My Live database Log File grows improperly. With in few hrs it increases from 1 GB to 10 GB. What's the Problem. How to trace the Problem and rectify where the improper growth occurs.
In what means the Log file Increases ?
Help Me,
Very Urgent
ASHOK S
-
There is most likely no problem at all.
The most common reason for this kind of growth is index rebuilds. Does this growth occur during a reindexing operation?
The other most likely candidate is some kind of bulk operation.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
1. What are you doing when it grows?
-As dcpeterson says, index rebuilds will do this. Do you have a maintenance plan running?
2. Are you doing backups (Full and Transaction Log backups)?
-SQLBill
-
One other thing, go to Enterprise Manager, expand until you see your database. Right click on your database, select Properties. Go to the Transaction Log tab. There will be a section for Autogrow. You can set the autogrow for either a percentage or MB. Which is your's set for and how much?
-SQLBill
-
Transaction Log has been set as Unrestricted Growth. It Auto grows frequently. We have 100 users on our Intranet applications. The Log File grows and fills the hard disk space. The free space on the hard disk is not that much. We have enabled the Option Auto Shrink. (Will this Shrink the Log File).
Is there any disadvantage in using this if it works.
How to trace the Transaction log events transaction by transaction.
Recently, Reindexing part has been not touched.
Help Me.
-
First aid:
Check the recovery model of your database. I suppose it is FULL recovery model. Do your have a lot of bulk inserts? Do you need all of them to log?
Consider use of some other recovery model for the first aid. (Simple or Bulk inserted).
Right click on database from EM, properties/options and there is recovery model settings.
Is all of you log active? Maybe you can truncate it? You can check this with:
from QA:
Use DataBAseNAme
DBCC SQLPERF(logspace).
and, of course use profiler to check what's going on.
Lynn
-
Forgot to mention:
Restrict file growth of transaction log is not such a good idea. I think you shouldn't change this option (unrestricted tlog growth). The better solution is to maintain tlog growth by truncating the log from time to time.
Let us know when you solve the problem.
-
I don't ever use the auto shrink option for production (and very seldom for dev and QA) servers. There are many things that can affect how fast and how large the transaction log grows. As long as you are doing periodic log backups, your log will reach a sort of equalibrium where it will grow gradually or maybe not at all. Trying to keep the log file smaller than this will normally be a losing battle.
It sounds like you need to get some more disk space allocated, make sure you are doing periodic log backups (how often depends on how much activity the database handles) and stop trying to force the transaction log to conform to some arbitrary size limitation.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
With 100 users using intranet application I cannot think of to many transactions generated in small interval of time.
How often you are taking log backups ?
Very high volume of transactions, Non comitted Transactions or Hung transaction, disk space are among the possibilities you need to check for.
Write Job that has 2 steps as shown below
Step 1 : Truncate log
Backup Log DbName With Truncate_Only
Dbcc Shrinkfile(DBName_log, 100)
Step: 2 Full DB backup
Backup Database DbName
To DBNameBackup
With Init, Name = 'DBNameBackup'
Schedule this job once in a day. Execute this job once after creation to resolve the problem.
Make sure you write another job to take log backup and schedule that initially for 15 min. interval.
Do not autoshrink user db or temp db, check autogrow is on and
for long run solution also ask appplication developer to look in the code
and bundle solution in small transactions.
Hope this helps
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
I don't see how you can recommend this without knowing much more about the specifics...
It generally makes no sense to repeatedly shrink the log file only to have it grow again. Taking log backups every 15 minutes is a bit of overkill for the vast majority of systems.
Ashok hasn't even given enough information to know if there IS a problem, so how can you solve it for him?
There is sometimes a fine line between solving peoples problems and merely providing them rope to hang themselves with...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Also, where is your log file located? And what is the size of the drive or partition that it is on?
-SQLBill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply