| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2007-05-30 : 11:02:40
|
| Hi,We have SQL 2000 Enterprise. I have several dbs that are log shipped to another cluster.One of the db which is relatively small is hanging up while restoring the db. It does not throw any errors. It sits in loading condition. Can anyone please help me on this please.The log shipping is happening only weekly as this is a low activity db. The logs are taken weekly and applied right after. The destination is on stand by mode. Log shipping is performed via MS log shipping.please help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-30 : 13:43:53
|
| Run the restore log command in Query Analyzer against the same file that log shipping is attempting to restore. Post the error here.BTW, running log shipping once a week is practically pointless.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2007-05-30 : 15:55:29
|
| Thanks Tara, for the tip. It is not generating any errors while restoring that log. Its just sitting there with loading condition - is what I heard from our network admin. We do not have a full fedged DBA for our prod. The network admin/manager with limited knowledge of SQL is reponsible for prod sql.I looked at the logs on that server and here is what I found.Error: 823, Severity: 24, State: 2I/O error (bad page ID) detected during read at offset 0x000002ece0e000 in file 'P:\Program Files\Microsoft SQL Server\MSSQL$XYZ\data\ABC.mdf'.Also, you are right regarding log-shipping weekly. We set it up this way just for convenience so that everything is automated for data warehouse loads. The data is shipped from different set of server cluster (Prod) to replication server cluster which feeds the data warehouse. I do not know another easier process.Thanks for all help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-30 : 16:42:57
|
| It looks like you've got database corruption in your ABC database, per the error.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-30 : 16:43:44
|
| Target db in log shipping is in loading status, otherwise will unable to restore new log backup. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-30 : 16:51:54
|
| That's how it's supposed to be. There are two statuses you want for restoring logs: standby and loading. Either way, you can apply additional transaction logs.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2007-06-05 : 13:45:42
|
| We have narrowed down the cause of the error. It looks like there was this optimization maitenance plan, which not necessary inflating the size of the logs. Due to the enormous size of the log, it was failing in between restore or perhaps corrupting it as Tara mentioned.We will have to wait and watch. I could have never guessed this. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 23:07:21
|
| What's log backup interval? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-07 : 11:58:56
|
| They are only doing it once a week, which is just pointless for log shipping. You should backup your tlog several times per day. It'll help keep the file size down too.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Log shipping failure - db in loading cond