July 7, 2015 at 4:42 am
While i execute dbcc sqlperf(logspace); I get following values.
Database NameLog Size (MB)Log Space Used (%)
master 16.17969 13.30275
tempdb 7.429688 61.7245
model 0.7421875 45.78947
msdb 5.554688 25.87904
distribution 2808.93 0.8172179
BANKDB 23438.87 48.20037
WSMIRSDB 109.7422 4.839111
For database BANKDB , Log Space used(%) is 48.83% and Log size is about 23438.87 where as my database size of BANKDB is 60 GB. FULL database and Log back is done every day night one time. My database is performing slow now.
Do we need to take log backup frequently like once a 1 hour so that Log space used will be less. Same query is taking more time to execute than before in same database is it because of log file has increased.
I do index organize and rebuild once a week and stats apply nightly.
Is it correct once log space size is increasing more than 10%. Do we need to take log backup?
Please suggest.
July 7, 2015 at 4:54 am
You need to do regular (more than daily log backups) not just to control the size of your log, but also to enable point-in-time recovery should it be needed. You will need to check any SLAs for the RPO (recovery point objective) and make sure you're backing up the log at least that frequently. So if your RPO is 15 minutes, that means no more than 15 minutes of data can be lost, and you need to make sure you do at least four log backups an hour. It's unlikely that a 48% full log will be causing your performance problems. You'll need to analyse your workload to see what the pain points are in terms of queries. It may turn out that weekly index maintenance isn't often enough.
John
July 7, 2015 at 5:00 am
Size of the log and high % use will not cause queries to run slowly.
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 7, 2015 at 6:05 am
Could you please tell me why same query is performing slow in peak transaction hour. However same query performs faster in staging of same database size as well as less peak hour.
Peak hour means there are more insert /select operation in same table from application.
July 7, 2015 at 9:19 am
Contention most likely. Could be blocking, could be other waits. You'll have to analyse and investigate.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply