Tuesday, June 11, 2013

T-log backups taking too long to run during Normal hours

Hi,
I had a Application running for 2 hours which does run SSIS Packages , Inserts, Updates, Deletes on few databases and i have a Symantec log backup job which runs every 3 hours ...

We had huge performance problems Disk I/O , CPU , MEMORY everything was high , so 


I added few indexes, constantly monitored with Activity monitor , the last step of Application is to delete from history database tables and then insert in History database tables which  is taking long time to complete  also at the same time in parallel Symantec was doing a log  backing up history database log file(LDF) and doing a constancy check.
So I was positive that when backups are running mainly when Symantec starts backup of History database doing a consistency check and at the same time when the Application is trying to access History, Process database tables the process is taking longer than expected.

So after lot of research I  suggested to take  out Consistency check from backups  and then  log backups ran in 1 min and also the application ran good, with no huge DISK I/O and CPU hikes ..

Tip here is: 
do not run Constancy check during normal business hours .its high Disk I/O and CPU operation ,...


Thanks
Naresh
06/11/2013

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...