July 17, 2013 at 9:45 am
I have a few databases that are log shipping from ServerA to ServerB where they are available as a Standby/Read Only.
Yesterday I dropped one index on ServerA.DatabaseA and created a brand new index on ServerA.DatabaseA
The Restore takes place at midnight and according to my history log and according to the errorlog, all the logs were applied to ServerB.DatabaseA
Yet, I do not see the newly created index and the old index is still on ServerB.DatabaseA
Please advise.
July 17, 2013 at 10:58 am
... maybe I need to run update statistics on the source server's table ? I'm puzzled.
July 17, 2013 at 11:09 am
thats not possible, are you sure the secondary is up to date? run this on the secondary
-- tells you last log copied and restored, primary and secondary server names
select * from msdb.dbo.log_shipping_monitor_secondary
---------------------------------------------------------------------
July 17, 2013 at 11:27 am
You're right and thank you. I checked and it seems that the last restored log file name was this morning at 1AM. The process started at midnight as it was supposed to but it did not seem to process all of the files. It stopped on a file from yesterday's 6AM job (that explains why my changes from yesterday at 10AM are missing)
\\ServerB\logship\DatabaseA_20130716100001.trn
Now I checked the even log or anything for explanation. I checked whether logs past this log exist (YES) whether they have been copied over (YES).
What would cause a log shipping job to stop at certain file and not continue to the next?
I am now assuming that the next files were available during that time on ServerB
July 17, 2013 at 11:29 am
Last copied file: \\ServerB\logship\DatabaseA_20130717160001.trn
Last copied date: 2013-07-17 13:00:18.150
July 17, 2013 at 12:24 pm
still trying to figure this out why the logs would just not process all the files. Curiously the SQL Error Log has a gap between 1:08AM and 9:50AM
07/17/2013 09:50:54,spid594,Unknown,Zeroing completed on F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf
07/17/2013 09:50:54,spid594,Unknown,Zeroing F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf from page 6552 to 7208 (0x3330000 to 0x3850000)
07/17/2013 01:08:23,spid663,Unknown,Setting database option MULTI_USER to ON for database DatabaseA.
07/17/2013 01:08:23,spid663,Unknown,FileHandleCache: 0 files opened. CacheSize: 8
07/17/2013 01:08:22,spid663,Unknown,Restore: Configuration section loaded
07/17/2013 01:08:22,spid663,Unknown,Opening backup set
07/17/2013 01:08:02,spid663,Unknown,X-locking database: DatabaseA
07/17/2013 01:08:02,spid663,Unknown,RestoreLog: Database DatabaseA
July 17, 2013 at 1:36 pm
oh well. I'll run a differential and apply it tonight to avoid loading so many logs at once. (this is an active 2TB database)
July 17, 2013 at 1:57 pm
you will only be able to do that if a full backup does not run after the log shipping broke and before you take your differential, so beware.
Has a log backup been taken outside of logshipping?
check log_shipping_monitor_history_detail and log_shipping_monitor_error_detail
Are you using a third party tool for this those are strange messages in the errorlog.
---------------------------------------------------------------------
July 17, 2013 at 2:13 pm
Log backup has not been taken outside of the LS log dumps so my logs should be ok to go.
I am in a new position and at this point I am not sure if we have other 3rd party tools accessing the SQL Server but my initial observations point to NO.
Full backup runs over the weekend so I should be ok with the differential.
I currently have another high profile user in the database almost 24/7 running multithreaded high intensity reads...I was slightly suspicious whether the user hasn't somehow interrupted the restore process to gain extra time to run the multithreaded jobs but I can not see anything in the logs that would prove this.
July 17, 2013 at 2:23 pm
hmm. by checking the log_shipping_monitor_error_detail I see an entry at 1AM that says:
Exclusive access could not be obtained because the database is in use. RESTORE LOG is terminating abnormally.
How is this possible when the LS settings are to disconnect users?
Is this a case where the LS job ran a disconnect script and before it could start restoring someone was able to get into the database?
Could this event break the whole job to a point where it just bonks out and does not retry?
July 17, 2013 at 2:26 pm
I have never seen messages like that in the errorlog before which made me ask about third party tools.
thats a point, is someone connected to the secondary preventing the restores running?
the msdb tables should tell you the exact cause
edit : posts overlapped!
---------------------------------------------------------------------
July 17, 2013 at 2:29 pm
if something is continually trying to connect it could do that.
time for sp_who2 🙂
---------------------------------------------------------------------
July 17, 2013 at 3:16 pm
I am pretty sure I know the source except I have provided a status sp and their code is supposed to loop until the status changes...oh well...time for a meeting 🙂
Thanks everyone for your help. Much appreciated.
July 17, 2013 at 3:30 pm
Everyone? Who did I miss?
---------------------------------------------------------------------
July 17, 2013 at 4:12 pm
hey texting and driving is bad I didn't notice it was just me and you
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply