April 21, 2014 at 8:55 am
Hi,
My log drive for db's are increasing.
Also, I ran the DBCC SQLPERF (logspace) which it is showing the log file size and %usage of every db.
All db's are using very less % of log file space.
Can any one please suggest what I have to do to reduce the log drive size.
April 21, 2014 at 9:08 am
A few things here. You say log drive, but I think you mean log files. Is that correct? The drive is the letter/path designation your Windows host sees. Like c:, d:, etc. These don't increase in size. The log files are the place where SQL Server writes log activity.
You say that the log space shows that each system using less than the full space of each file, is that correct? If so, then what is increasing?
I realize there may be a language barrier here, but you need to distinguish between drives, log files and other items, such as free space on the drive.
April 21, 2014 at 3:03 pm
It depends. If you reduce the log file size and it grows back to the original size again, you've just wasted resources, and re-fragmented the file as well.
Therefore, unless you've done some unusual processing, you may need to accept that for now the log files will need more space overall.
Shrink the log files back to 1MB, then re-grow them to, say, 75% of what they have now. This will both reduce the size, at least temporarily, defragment the file and reduce the number of VLFs.
If a log file(s) grows again, you'll need to change your processes or allocate more log space.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 25, 2014 at 2:12 am
Your log files grow dependent upon transactions being done.
If your database recovery model is set to SIMPLE then the log file tends to grow and then stay at a static size, unless you have unusual activity.
However, if your recovery model is FULL then the log file will grow until you do a transaction log backup. This will clear the completed transactions out of the file but will not change the size of the file.
Hope some of that made sense....:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply