So, I have setup log shipping and it works fine for a while (like a1-2 days
and then one time when it is trying to restore the data it freezes and the
database says it is (Loading) and it does nothing. At all. Just sits there.
Any thoughts?
Willie
Are you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
mark the database as "Loading", since you haven't completed the restore phase yet.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
> So, I have setup log shipping and it works fine for a while (like a1-2 days and then one time when
> it is trying to restore the data it freezes and the database says it is (Loading) and it does
> nothing. At all. Just sits there. Any thoughts?
> Willie
>
|||On Apr 16, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
> mark the database as "Loading", since you haven't completed the restore phase yet.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>
>
> - Show quoted text -
How big are the logs at this point? I have seen this before when the
logs being shipped are huge and they take a long time to get to the
standby and restore. Just an idea, it may be working but slowly?
Maybe look at the sysprocesses table and see what is happening.
(master db)
Kristina
|||So, I went ahead and let it sit and the database 'kind of recovered' at
least it no longer in the loading state, but the recoveries loading stopped
working. The largest log file is about 1 mb and I restore them every 15
minutes. What is confusing to me is that I did this thru the Log Shipping
wizard and everything worked fine for a few days and then the log files
stopped loading after they were copied over. So, it had loaded approximately
200 log files before it decided to stop.
I guess I may have had one large log file there when I generated a few
hundred thousand serial numbers, not sure of the timing of that. Any
suggestions on what to do when something like that pops up? It seems that if
there is a large spike in the log file size then perhaps it chokes? Is there
any way to setup log shipping dependent on log file size rather than time
based? Thanks again for the help.
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegro ups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
|||OK, a little more info. A fairly large file (about 62 mb) was copied and
restored just fine as were two more small (like 717 kb) log files. And then
suddenly on the next restore, after 2 days of running just fine every 15
minutes, it hangs on 'Loading', a small file! I checked sysprocesses and
there really isn't anything that stands out to me there, but I honestly
don't know for sure what I should be looking for...
Any thoughts? things I should look at?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegro ups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
|||OK, so how about this, here is the error message from the failed loading
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL
Server Driver][SQL Server]The media family on device
'F:\mssql\mssql\backup\onyx\Onyx_tlog_200704191500 .TRN' is incorrectly
formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database file
'F:\mssql\mssql\backup\onyx\Onyx.tuf'.
I was incorrect, this is the 62 mb file, the one that failed to load. I've
noticed that I seem to have this issue if I have a long running process
going when log shipping wants to ship a log over. Is there any way to
suspend the log shipping so that I don't ahve this issue, or any other work
around?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegro ups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
Showing posts with label shipping. Show all posts
Showing posts with label shipping. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
Restore problem
So, I have setup log shipping and it works fine for a while (like a1-2 days
and then one time when it is trying to restore the data it freezes and the
database says it is (Loading) and it does nothing. At all. Just sits there.
Any thoughts?
WillieAre you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
mark the database as "Loading", since you haven't completed the restore phase yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
> So, I have setup log shipping and it works fine for a while (like a1-2 days and then one time when
> it is trying to restore the data it freezes and the database says it is (Loading) and it does
> nothing. At all. Just sits there. Any thoughts?
> Willie
>|||On Apr 16, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
> mark the database as "Loading", since you haven't completed the restore phase yet.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>
> > So, I have setup log shipping and it works fine for a while (like a1-2 days and then one time when
> > it is trying to restore the data it freezes and the database says it is (Loading) and it does
> > nothing. At all. Just sits there. Any thoughts?
> > Willie- Hide quoted text -
> - Show quoted text -
How big are the logs at this point? I have seen this before when the
logs being shipped are huge and they take a long time to get to the
standby and restore. Just an idea, it may be working but slowly?
Maybe look at the sysprocesses table and see what is happening.
(master db)
Kristina|||So, I went ahead and let it sit and the database 'kind of recovered' at
least it no longer in the loading state, but the recoveries loading stopped
working. The largest log file is about 1 mb and I restore them every 15
minutes. What is confusing to me is that I did this thru the Log Shipping
wizard and everything worked fine for a few days and then the log files
stopped loading after they were copied over. So, it had loaded approximately
200 log files before it decided to stop.
I guess I may have had one large log file there when I generated a few
hundred thousand serial numbers, not sure of the timing of that. Any
suggestions on what to do when something like that pops up? It seems that if
there is a large spike in the log file size then perhaps it chokes? Is there
any way to setup log shipping dependent on log file size rather than time
based? Thanks again for the help.
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, a little more info. A fairly large file (about 62 mb) was copied and
restored just fine as were two more small (like 717 kb) log files. And then
suddenly on the next restore, after 2 days of running just fine every 15
minutes, it hangs on 'Loading', a small file! I checked sysprocesses and
there really isn't anything that stands out to me there, but I honestly
don't know for sure what I should be looking for...
Any thoughts? things I should look at?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, so how about this, here is the error message from the failed loading
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL
Server Driver][SQL Server]The media family on device
'F:\mssql\mssql\backup\onyx\Onyx_tlog_200704191500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database file
'F:\mssql\mssql\backup\onyx\Onyx.tuf'.
I was incorrect, this is the 62 mb file, the one that failed to load. I've
noticed that I seem to have this issue if I have a long running process
going when log shipping wants to ship a log over. Is there any way to
suspend the log shipping so that I don't ahve this issue, or any other work
around?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
and then one time when it is trying to restore the data it freezes and the
database says it is (Loading) and it does nothing. At all. Just sits there.
Any thoughts?
WillieAre you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
mark the database as "Loading", since you haven't completed the restore phase yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
> So, I have setup log shipping and it works fine for a while (like a1-2 days and then one time when
> it is trying to restore the data it freezes and the database says it is (Loading) and it does
> nothing. At all. Just sits there. Any thoughts?
> Willie
>|||On Apr 16, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you sure that this restore was made using STANDBY option? If you used NORECOVERY, then EM will
> mark the database as "Loading", since you haven't completed the restore phase yet.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>
> > So, I have setup log shipping and it works fine for a while (like a1-2 days and then one time when
> > it is trying to restore the data it freezes and the database says it is (Loading) and it does
> > nothing. At all. Just sits there. Any thoughts?
> > Willie- Hide quoted text -
> - Show quoted text -
How big are the logs at this point? I have seen this before when the
logs being shipped are huge and they take a long time to get to the
standby and restore. Just an idea, it may be working but slowly?
Maybe look at the sysprocesses table and see what is happening.
(master db)
Kristina|||So, I went ahead and let it sit and the database 'kind of recovered' at
least it no longer in the loading state, but the recoveries loading stopped
working. The largest log file is about 1 mb and I restore them every 15
minutes. What is confusing to me is that I did this thru the Log Shipping
wizard and everything worked fine for a few days and then the log files
stopped loading after they were copied over. So, it had loaded approximately
200 log files before it decided to stop.
I guess I may have had one large log file there when I generated a few
hundred thousand serial numbers, not sure of the timing of that. Any
suggestions on what to do when something like that pops up? It seems that if
there is a large spike in the log file size then perhaps it chokes? Is there
any way to setup log shipping dependent on log file size rather than time
based? Thanks again for the help.
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, a little more info. A fairly large file (about 62 mb) was copied and
restored just fine as were two more small (like 717 kb) log files. And then
suddenly on the next restore, after 2 days of running just fine every 15
minutes, it hangs on 'Loading', a small file! I checked sysprocesses and
there really isn't anything that stands out to me there, but I honestly
don't know for sure what I should be looking for...
Any thoughts? things I should look at?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, so how about this, here is the error message from the failed loading
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL
Server Driver][SQL Server]The media family on device
'F:\mssql\mssql\backup\onyx\Onyx_tlog_200704191500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database file
'F:\mssql\mssql\backup\onyx\Onyx.tuf'.
I was incorrect, this is the 62 mb file, the one that failed to load. I've
noticed that I seem to have this issue if I have a long running process
going when log shipping wants to ship a log over. Is there any way to
suspend the log shipping so that I don't ahve this issue, or any other work
around?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Are you sure that this restore was made using STANDBY option? If you used
>> NORECOVERY, then EM will
>> mark the database as "Loading", since you haven't completed the restore
>> phase yet.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
>> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>>
>> > So, I have setup log shipping and it works fine for a while (like a1-2
>> > days and then one time when
>> > it is trying to restore the data it freezes and the database says it is
>> > (Loading) and it does
>> > nothing. At all. Just sits there. Any thoughts?
>> > Willie- Hide quoted text -
>> - Show quoted text -
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
Restore problem
So, I have setup log shipping and it works fine for a while (like a1-2 days
and then one time when it is trying to restore the data it freezes and the
database says it is (Loading) and it does nothing. At all. Just sits there.
Any thoughts?
WillieAre you sure that this restore was made using STANDBY option? If you used NO
RECOVERY, then EM will
mark the database as "Loading", since you haven't completed the restore phas
e yet.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
> So, I have setup log shipping and it works fine for a while (like a1-2 day
s and then one time when
> it is trying to restore the data it freezes and the database says it is (L
oading) and it does
> nothing. At all. Just sits there. Any thoughts?
> Willie
>|||On Apr 16, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you sure that this restore was made using STANDBY option? If you used
NORECOVERY, then EM will
> mark the database as "Loading", since you haven't completed the restore ph
ase yet.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>
>
>
> - Show quoted text -
How big are the logs at this point? I have seen this before when the
logs being shipped are huge and they take a long time to get to the
standby and restore. Just an idea, it may be working but slowly?
Maybe look at the sysprocesses table and see what is happening.
(master db)
Kristina|||So, I went ahead and let it sit and the database 'kind of recovered' at
least it no longer in the loading state, but the recoveries loading stopped
working. The largest log file is about 1 mb and I restore them every 15
minutes. What is confusing to me is that I did this thru the Log Shipping
wizard and everything worked fine for a few days and then the log files
stopped loading after they were copied over. So, it had loaded approximately
200 log files before it decided to stop.
I guess I may have had one large log file there when I generated a few
hundred thousand serial numbers, not sure of the timing of that. Any
suggestions on what to do when something like that pops up? It seems that if
there is a large spike in the log file size then perhaps it chokes? Is there
any way to setup log shipping dependent on log file size rather than time
based? Thanks again for the help.
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, a little more info. A fairly large file (about 62 mb) was copied and
restored just fine as were two more small (like 717 kb) log files. And then
suddenly on the next restore, after 2 days of running just fine every 15
minutes, it hangs on 'Loading', a small file! I checked sysprocesses and
there really isn't anything that stands out to me there, but I honestly
don't know for sure what I should be looking for...
Any thoughts? things I should look at?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, so how about this, here is the error message from the failed loading
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]The media family on device
'F:\mssql\mssql\backup\onyx\Onyx_tlog_20
0704191500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database
file
'F:\mssql\mssql\backup\onyx\Onyx.tuf'.
I was incorrect, this is the 62 mb file, the one that failed to load. I've
noticed that I seem to have this issue if I have a long running process
going when log shipping wants to ship a log over. Is there any way to
suspend the log shipping so that I don't ahve this issue, or any other work
around?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
and then one time when it is trying to restore the data it freezes and the
database says it is (Loading) and it does nothing. At all. Just sits there.
Any thoughts?
WillieAre you sure that this restore was made using STANDBY option? If you used NO
RECOVERY, then EM will
mark the database as "Loading", since you haven't completed the restore phas
e yet.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
> So, I have setup log shipping and it works fine for a while (like a1-2 day
s and then one time when
> it is trying to restore the data it freezes and the database says it is (L
oading) and it does
> nothing. At all. Just sits there. Any thoughts?
> Willie
>|||On Apr 16, 1:05 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Are you sure that this restore was made using STANDBY option? If you used
NORECOVERY, then EM will
> mark the database as "Loading", since you haven't completed the restore ph
ase yet.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:usFKfeDgHHA.4692@.TK2MSFTNGP03.phx.gbl...
>
>
>
> - Show quoted text -
How big are the logs at this point? I have seen this before when the
logs being shipped are huge and they take a long time to get to the
standby and restore. Just an idea, it may be working but slowly?
Maybe look at the sysprocesses table and see what is happening.
(master db)
Kristina|||So, I went ahead and let it sit and the database 'kind of recovered' at
least it no longer in the loading state, but the recoveries loading stopped
working. The largest log file is about 1 mb and I restore them every 15
minutes. What is confusing to me is that I did this thru the Log Shipping
wizard and everything worked fine for a few days and then the log files
stopped loading after they were copied over. So, it had loaded approximately
200 log files before it decided to stop.
I guess I may have had one large log file there when I generated a few
hundred thousand serial numbers, not sure of the timing of that. Any
suggestions on what to do when something like that pops up? It seems that if
there is a large spike in the log file size then perhaps it chokes? Is there
any way to setup log shipping dependent on log file size rather than time
based? Thanks again for the help.
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, a little more info. A fairly large file (about 62 mb) was copied and
restored just fine as were two more small (like 717 kb) log files. And then
suddenly on the next restore, after 2 days of running just fine every 15
minutes, it hangs on 'Loading', a small file! I checked sysprocesses and
there really isn't anything that stands out to me there, but I honestly
don't know for sure what I should be looking for...
Any thoughts? things I should look at?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>|||OK, so how about this, here is the error message from the failed loading
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]The media family on device
'F:\mssql\mssql\backup\onyx\Onyx_tlog_20
0704191500.TRN' is incorrectly
formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
[Microsoft][ODBC SQL Server Driver][SQL Server]Deleting database
file
'F:\mssql\mssql\backup\onyx\Onyx.tuf'.
I was incorrect, this is the 62 mb file, the one that failed to load. I've
noticed that I seem to have this issue if I have a long running process
going when log shipping wants to ship a log over. Is there any way to
suspend the log shipping so that I don't ahve this issue, or any other work
around?
Willie
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1176765567.677652.11640@.b75g2000hsg.googlegroups.com...
> On Apr 16, 1:05 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How big are the logs at this point? I have seen this before when the
> logs being shipped are huge and they take a long time to get to the
> standby and restore. Just an idea, it may be working but slowly?
> Maybe look at the sysprocesses table and see what is happening.
> (master db)
> Kristina
>
Restore Previous Backup on Logshipping Primary
Quick Question: If I want to restore an earlier database backup (rollback)
my log shipping primary - how do I ensure that I get my secondaries in sync?
Just re-create the maintenance plan? The database in question is very large,
and I'd like to be able to just do a point-in-time restore on the primary,
etc, without messing up my transaction log lineage.
Does anyone have any scripts handy?
TIA
J Roberts
Norfolk ITJohn
Backup Log file before restore full database
Disable Log Shipping
Restore the database and apply all log file/s with option at point of
time. Then enable Log Shipping and restore full and then log file backpus on
the secondary server
Actually I have not used maintenance paln fo doing Log Shipping , as I
always create scripts (stored proceduers etc..)
"John Roberts" <JohnRoberts@.discussions.microsoft.com> wrote in message
news:094ACA2D-1C92-47DE-B63C-7E7A4087FE7B@.microsoft.com...
> Quick Question: If I want to restore an earlier database backup
> (rollback)
> my log shipping primary - how do I ensure that I get my secondaries in
> sync?
> Just re-create the maintenance plan? The database in question is very
> large,
> and I'd like to be able to just do a point-in-time restore on the primary,
> etc, without messing up my transaction log lineage.
> Does anyone have any scripts handy?
> TIA
> J Roberts
> Norfolk IT|||That's what I thought. You'd think that MS would have considered rollbacks
in their implementation... Oh well. T-SQL it is. I think I'm going to use
SQL-DMO (vb.net) for this...
Thanks for your reply.
John
"Uri Dimant" wrote:
> John
> Backup Log file before restore full database
> Disable Log Shipping
> Restore the database and apply all log file/s with option at point of
> time. Then enable Log Shipping and restore full and then log file backpus on
> the secondary server
> Actually I have not used maintenance paln fo doing Log Shipping , as I
> always create scripts (stored proceduers etc..)
>
>
> "John Roberts" <JohnRoberts@.discussions.microsoft.com> wrote in message
> news:094ACA2D-1C92-47DE-B63C-7E7A4087FE7B@.microsoft.com...
> > Quick Question: If I want to restore an earlier database backup
> > (rollback)
> > my log shipping primary - how do I ensure that I get my secondaries in
> > sync?
> > Just re-create the maintenance plan? The database in question is very
> > large,
> > and I'd like to be able to just do a point-in-time restore on the primary,
> > etc, without messing up my transaction log lineage.
> >
> > Does anyone have any scripts handy?
> >
> > TIA
> >
> > J Roberts
> > Norfolk IT
>
>
my log shipping primary - how do I ensure that I get my secondaries in sync?
Just re-create the maintenance plan? The database in question is very large,
and I'd like to be able to just do a point-in-time restore on the primary,
etc, without messing up my transaction log lineage.
Does anyone have any scripts handy?
TIA
J Roberts
Norfolk ITJohn
Backup Log file before restore full database
Disable Log Shipping
Restore the database and apply all log file/s with option at point of
time. Then enable Log Shipping and restore full and then log file backpus on
the secondary server
Actually I have not used maintenance paln fo doing Log Shipping , as I
always create scripts (stored proceduers etc..)
"John Roberts" <JohnRoberts@.discussions.microsoft.com> wrote in message
news:094ACA2D-1C92-47DE-B63C-7E7A4087FE7B@.microsoft.com...
> Quick Question: If I want to restore an earlier database backup
> (rollback)
> my log shipping primary - how do I ensure that I get my secondaries in
> sync?
> Just re-create the maintenance plan? The database in question is very
> large,
> and I'd like to be able to just do a point-in-time restore on the primary,
> etc, without messing up my transaction log lineage.
> Does anyone have any scripts handy?
> TIA
> J Roberts
> Norfolk IT|||That's what I thought. You'd think that MS would have considered rollbacks
in their implementation... Oh well. T-SQL it is. I think I'm going to use
SQL-DMO (vb.net) for this...
Thanks for your reply.
John
"Uri Dimant" wrote:
> John
> Backup Log file before restore full database
> Disable Log Shipping
> Restore the database and apply all log file/s with option at point of
> time. Then enable Log Shipping and restore full and then log file backpus on
> the secondary server
> Actually I have not used maintenance paln fo doing Log Shipping , as I
> always create scripts (stored proceduers etc..)
>
>
> "John Roberts" <JohnRoberts@.discussions.microsoft.com> wrote in message
> news:094ACA2D-1C92-47DE-B63C-7E7A4087FE7B@.microsoft.com...
> > Quick Question: If I want to restore an earlier database backup
> > (rollback)
> > my log shipping primary - how do I ensure that I get my secondaries in
> > sync?
> > Just re-create the maintenance plan? The database in question is very
> > large,
> > and I'd like to be able to just do a point-in-time restore on the primary,
> > etc, without messing up my transaction log lineage.
> >
> > Does anyone have any scripts handy?
> >
> > TIA
> >
> > J Roberts
> > Norfolk IT
>
>
Subscribe to:
Posts (Atom)