April 6, 2011 at 12:43 am
hi
our log file sizes are becoming huge, And our databases are configured for mirroring. what else way, we can truncate the log files?
Our system is a automated reconcilation system. where in we will load huge volumes of data from different sources. compare and find the breaks. we will store only the comparision results.. all other loaded datas will be deleted from the system.
We have two databases. One to store comparision rules and results(say ruledb). another(say tempdb) for temparory datas to load.
Data in the ruledb are confidential and important. And at the time of any disasters we need those data to be immedietly available at the other end.
So we enabled mirroring over that database.
All our comparision procedures will be in the rulesdb, it refers the tables from the tempdb using synonyms and compares it.
Even then our rulesdb's log file size is getting increased tremendously.
Please suggest some solutions.
April 6, 2011 at 2:07 am
Take more frequent transaction log backups and then find the sweet spot for size. e.g. After 2 hours of work it is 10gb so size it to 12gb and keep backing up the transaction log every two hours.
Where I work we generate 200gb of transaction log per day but our log file is ony 45gb as we backup every 15 minutes and our database is also mirrored.
April 6, 2011 at 2:15 am
vmssanthosh (4/6/2011)
hiour log file sizes are becoming huge, And our databases are configured for mirroring. what else way, we can truncate the log files?
Back up the log regularly. 15 minutes, 10 minutes, I've seen systems doing log backups every 5 minutes.
If the system is important enough to be mirrored then I assume the data is critical. If that's the case, then you need log backups, to disk, retained for long enough that you can always recover from disaster by restoring backups.
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
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply