July 10, 2012 at 4:25 am
I have inherited he administration of a 7*24 database (0 downtime in the last two years) which uses a combination of mirroring (intra-site failover) and merge replication (inter-site failover). The key database is 164GB in size with a 25GB transaction log comprising 2 files: 1 210MB the other 25GB and almost 1600 VLFs, which accounts for 70% of write I/O. I am concerned about the impact of log fragmentation on write performance. I want to recreate the transaction log with 1 file, with a total size of 25GB and autogrowth of 8GB to give me a reasonable number (approximately 50) or reasonably sized (512MB) VLFs.
I would welcome some guidance on the approach to take to achieve without impacting on availability.
July 10, 2012 at 4:33 am
Sounds like a reasonable plan.
Find the quietest time you can, make sure that all of the active log is in the log file you want to keep, drop the one you don't want, ensure that a log backup has just happened, shrink the one you're keeping to as close to 0 as possible, grow back to the size you want in chunks to give you the VLFs you want.
Test out on a test server first, it won't cause downtime, but you may slow things down while the log re-grows. You may want to do the growth in the smallest chunks that will give you the VLFs you want.
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
July 10, 2012 at 5:10 am
Thanks for the response.
Am I right in thinking I would need to:
ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionlogfileIwanttokeep
, SIZE = 25GB
,FILEGROWTH = 8GB
...
)
after shrinking the transactionlogfileIwanttokeep
July 10, 2012 at 5:26 am
Something like that. Check Kimberly Tripp's Transaction Log Throughput article for the formulae for VLFs, make sure you get the number you want.
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
July 10, 2012 at 5:31 am
Marvelous - thanks again. I have been using that article as my primary source so I think I am just about there then.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply