November 2, 2009 at 2:56 pm
Hi,
We have SQL Server 2005 EE x64 with SP3 and we are using log shipping for Disaster recovery. I got the below error but I’m unable to find the cause of the failure
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:14420
Date:11/1/2009
Time:9:16:05 AM
User:Abc\mssqlsrv
Computer:BG32
Description:
The log shipping primary database BG32.Mydb1 has backup threshold of 60 minutes and has not performed a backup log operation for 61 minutes. Check agent log and logshipping monitor information.
At 9:00 AM, Rebuild indexes Maintenance task job is running, and its taking 1hr 20 mins to complete that job. There are 10 databases, in which only Mydb1 is the biggest database of size 30GB and I’m using Maintenance plan Rebuild index task to rebuild indexes for all database on every Sunday.
My question is: Is log backup should NOT occur while Rebuild index job running??
Please advise me.
thanks
November 2, 2009 at 3:48 pm
Log backups can occur whilst indexes are rebuilt, in fact it is more important that they do as reindex can produce a lot of log.
Is your log backup frequency also hourly? I would hazard a guess it is and because the log file is big due to the reindex it has taken a little while to backup and exceeded the threshold.
I tend to make my threshold values twice my backup frequency so I am alerted if 2 log backups in a row have not happened.
---------------------------------------------------------------------
November 2, 2009 at 4:50 pm
Is your log backup frequency also hourly? I would hazard a guess it is and because the log file is big due to the reindex it has taken a little while to backup and exceeded the threshold.
I tend to make my threshold values twice my backup frequency so I am alerted if 2 log backups in a row have not happened.
Thank you..I got what the problem is. Our log shipping backup job occurs every 1 hr. For this particular database, I have selected the option "alert if no backup occurs with in" as 60 mins and for rest of the databases it is 180 mins(3 times the Log backup job frequency). After changing alert if no backup occurs with in" to 180 mins for Mydb1, even if the rebuild task takes place, I'm getting NO error/alert.
Is selecting the option "alert if no backup occurs with in" as 3 times the log backup job frequency makes sense? OR do I need to change this option to 2 times the log backup job frequency?
Thank you so much.
November 2, 2009 at 4:55 pm
Its a personal choice. As hourly is quite a low frequency I would go for just over twice the backup frequency.
---------------------------------------------------------------------
November 2, 2009 at 5:15 pm
Mydb1 is the biggest database of size 30GB and I’m using Maintenance plan Rebuild index task to rebuild indexes for all database on every Sunday.
I have the Log shipping Backup, copy & restore job schedule as below:
Backup job--->every 1hr starting from 3:15 AM to 3:00 AM
Copy job------>every 1hr starting from 3:20 AM to 3:00 AM
Restore job--->every 1hr starting from 3:50 AM to 3:00 AM
I have kept only 5 mins gap between Backup job & Copy job because the log backup file is very small and it will take always< 1min (except the time when rebuild index takes place)
I have kept only 30 mins gap between Copy job & Restore job because the log backup file is very small and it will take always< 5mins to Copy to Secondary server (except the time when rebuild index takes place, because at this time the log backup size will be almost 15GB)
For this Mydb1 database, during the index rebuild, its genarating huge log and the next log backup size is alomsot 15GB and that 15GB log transfer to secondary is taking 6 hrs and then restore taking an hour.
At this time I'm getting continuous errors saying saying that Backup threshold & Restore threshold are exceeded.
My question is: Is there a way to adjust the schedules for Log shipping jobs during the index rebuild so that there will be NO errors?? What is the best practice in this scenario?
thank you so much
November 3, 2009 at 1:37 am
could you plz advice
November 3, 2009 at 2:59 am
Mamta,
Before that I am curious to know why your log is growing, is your Recovery model Full while re-indexing? and also how many indexes have u got and are you indexing all of them?
November 3, 2009 at 3:22 am
there is no way to change the thresholds just for the time the reindex is running. At least you know why the threshold is being breached.
Krishna is on the right path. Look into separating up your reindex job, doing different tables at different times. Only reindex those indexes that actually require it, don't bother with tables < about 1000 pages. there are scripts on this site for more intelligent reindex jobs than the maintenance plan will build for you.
Schedule more log backups during the reindex phase, this could be done by putting executions of the log backup job in the script to reindex and/or setting SQLagent performance alerts to kick off the job if the transaction log gets too full.
---------------------------------------------------------------------
November 3, 2009 at 9:38 am
Schedule more log backups during the reindex phase, this could be done by putting executions of the log backup job in the script to reindex and/or setting SQLagent performance alerts to kick off the job if the transaction log gets too full
I'm interested to go with this option. Could you please point me to some documentation on how to do this.
thanks so much..
November 3, 2009 at 9:50 am
for performance thresholds search 'performance condition alerts [SQL Server Agent]' in BOL
for a base script to reindex see the one under sys.dm_db_index_physical_stats in BOL or any number of scripts on this site.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply