June 10, 2014 at 8:52 am
my database has:
1st log file is 5 GB and free space 600 MB
2nd log file is 140GB and free space 31GB
The database has 1800 VLFs.
According to Glenn Berry to reduce VLF --
Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
He also said:
Step 3: Grow the log file back to the desired size
My question is IN MY SITUATION what's the 'desirable size' would be? another word what size should I give to log file after above process?
June 10, 2014 at 9:03 am
Why 2 log files??...
Coming to the desired size for your LDF file, it really depends on your environment. There is no fixed number as a best practice which fits for all databases. Think about the single largest transaction on your database and then size your T-Log accordingly. Basic idea is not to have your T-Log Auto grow frequently. Shrinking your T-Log should be a very rare operation as well, not something which you should do on a regular basis.
June 10, 2014 at 9:24 am
smtzac (6/10/2014)
My question is IN MY SITUATION what's the 'desirable size' would be? another word what size should I give to log file after above process?
The max amount of space that the log requires, based on your operations and the frequency of your log backups. You'd need to minitor for a week or more to identify what that value is.
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
June 10, 2014 at 10:00 am
2nd log file is 140GB and free space 31GB
Wow, that's a lot of used log space.
Make sure you are taking proper log backups and/or that you don't have an old transaction "stuck" somehow that is preventing the log space from being marked as re-useable.
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".
June 10, 2014 at 10:07 am
[dup]
June 10, 2014 at 10:07 am
sreekanth bandarla (6/10/2014)
Why 2 log files??...
I also wondered this. I assume the first one filled up and you had to add another one? If so that indicates that you might have it in Full recovery model but you're not backing up the transaction log. Is that right? Because that might explain your log file growth.
June 11, 2014 at 7:19 am
I never heard that can't have 2 log files. May be I am wrong. 1st drive was getting full so I added another drive
June 11, 2014 at 7:20 am
i have T-log backup job every hour.
June 11, 2014 at 7:22 am
Yes, one filled so I added another one. It is full recovery model and backing up T log every hour. What's the ideal size would be for log file?
June 11, 2014 at 7:31 am
As Gail said, monitor for a week. If you can't wait, query msdb.dbo.backupset to find out what the sizes of your log backups have been. Make sure you include any periods of activity that would cause the highest log growth, such as index maintenance and month-end processing. Take the largest backup size, add something between 10 and 50% for contingency, and that's the size your log needs to be. This all assumes that you've been running hourly backups for the period that you're querying.
John
June 11, 2014 at 8:00 am
You can have two log files, but it doesn't directly benefit you. When the first drive is (near) full, though, adding a second log file on a different drive is needed and I wouldn't worry about that for now.
The important thing now is to verify that the log space can be re-used, or to see why it can't:
SELECT name, log_reuse_wait_desc, is_read_committed_snapshot_on,
recovery_model_desc --just to be absolutely sure
FROM sys.databases
WHERE
name = '<your_db_name>'
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".
June 11, 2014 at 8:49 am
Total DB size is 790 GB. doesn't this help? My question is what's the appropriate size for this large db after shrinking the log file.
June 11, 2014 at 8:54 am
[more effing duplicates, sorry]
June 11, 2014 at 8:54 am
smtzac (6/11/2014)
I never heard that can't have 2 log files. May be I am wrong. 1st drive was getting full so I added another drive
No, as already been said, you can but there's no point really since it is written to sequentially not in parallel.
There is no one formula to say how big your log size should be. Do the thing that ScottPletcher suggested above to try to work out why your log is not being reused. That's much more important than plucking a number out of the air.
June 11, 2014 at 9:02 am
smtzac (6/11/2014)
Total DB size is 790 GB. doesn't this help? My question is what's the appropriate size for this large db after shrinking the log file.
I'm afraid it doesn't help. You can have a large database with very little update activity. Conversely, you can have a small database where changes are happening all the time. Transaction log size is a function of rate of changes made to the database, and frequency of transaction log backups. We've already shown you how to estimate a size for your log files. As Scott and Beatrix also suggested, if (parts of) your log is being marked as unavailable for reuse, you also need to investigate that, otherwise your log may grow larger than you expected. Possible reasons for that could include replication or long-running or uncommitted transactions.
John
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply