SQL Server Performance Forum – Threads Archive

Log Shipping DB stuck in (Loading) Mode

I have followed the steps for Log Shipping in the "How to Perform SQL Server Log Shipping" article and am having a problem.
The orginal DB is backed up fine, copied to backup server fine, and appears to be restored fine (based on file times and sizes) but the job finished the the database is stuck in (Loading) Mode.
Here is my SP for the restore.
—
CREATE PROCEDURE restore_database_backups AS RESTORE DATABASE database_name
FROM DISK = ‘d

ata SQL ServerMSSQLStandbyStandbyDBBackup.bak’
WITH
DBO_ONLY,
REPLACE,
STANDBY = ‘d

ata SQL ServerMSSQLStandbyundo_StandbyDBBackup.ldf’,
MOVE ‘Fakename_dat’ TO ‘d

ata SQL ServerMSSQLDatafakename.mdf’,
MOVE ‘Fakename_log’ TO ‘f:SQL2kLogsfakename.ldf’ WAITFOR DELAY ’00:00:05′ EXEC sp_dboption ‘Fakename’, ‘single user’, true —
I changed the database name for this email, but you get the idea. If I try and go anything in query analyzer it will come back that this DB is only partically restored. Any Ideas? Thanks!

BOL refers:
In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility. Use with the RECOVERY option.
.. follow as suggested and to set database options refer to SP_DBOPTION. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Ok I replaced DBO_ONLY with RESTRICTED_USER – Same results stuck in loading mode. Where should I use the Recovery Option? And what Database options should I set? Thanks!

Use RECOVERY option after WITH in the restore statement, for instance :
RESTORE Database MyNwind
FROM MyNwind1
WITH RECOVERY
I mean to say if you need to set any of the database options then use SP_DBOPTION to set. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Just my experience with the logshipping, if you restore full database or the transaction log on the standby server it works perfect and everything is restored perfectly but again if I try to restore the backups through the restore option in the Enterprise Manager and after restoring I get the loading warning and nothing happened. The Enterprise Manager shows the database is still loading but actually it’s been load and the status never changes. Thanx.
Muneeb.

Well I added the recovery and removed the standby and still get the same results. I am running this sp in a job. It looks like it restored as the file sizes are right and dates and times of files are right.
Muneeb – How do I change the status out of loading if you have seen this before? Thanks

Hotot- To answer your question what I did with my server was instead of restoring the whole database back again and applying all the transaction logs one by one, I restored the latest transaction log which I need to apply since my database was already upto date before the loading….status and the status changes. Because, my database is so big that just to restore it takes quite a bit of time and then the huge files of transaction logs as well. So, I came up with the above approach and it worked. Thanx.

Hotot
Have you tried to access the database after restoring the last transaction log using WITH RECOVERY option. I would support Muneeb’s approach in this regard. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for all your help… but so far no luck.
I tried restoring the backup manually now and get the same problem… When restoring the progress bar never moves either till it just says done and is stuck ‘loading’. I tried doing a RESTORE DATABASE fakename WITH RECOVERY from QA and I get File ‘fakename_dat’ was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. The backup process seems fine. I just did what it said in the log shipping article BACKUP LOG fakename WITH TRUNCATE_ONLY
WAITFOR DELAY ’00:00: BACKUP DATABASE fakename TO StandbyDBBackup WITH INIT
WAITFOR DELAY ’00:00:05′ I copies over fine after that also. Is something wrong with my backups? satya – I did try doing this with the WITH RECOVERY option and still had same results. Muneeb – Right now it is set up to do a complete DB backup everynight (DB is about 400megs) then logs every hour. This process was working, set up by someone using this article years ago and the server it was running the backup on when down. They had backed up everything but the backup procedure so notes pointed me to this article for how it was orginally set up. I think I need to have the whole DB restored properly before I can apply new transactions logs? Thanks

Yes initially FULL Database restore should be completed and then only Transaction log backup can carry over properly. BTW, are there any changes happened recently to SQL server & OS? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes, if the suggestion I gave you earlier won’t work then ultimately you have to restore the full backup and then apply the transaction logs one by one. In my experience with log shipping, it’s always better to restore the most recent backup and then apply the logs from there. This way you save a heck of a time. But, it’s also a better approach as I did during setting up my log shipping to restore an old backup and apply the logs till now so that one can make sure that everything is in order and the transaction chain isn’t broken till now. Thanx Muneeb.

Well I still have not been able to get the entire DB to restore. It always comes back in (loading) mode. However I was able to restore the file using EM. I set it to read-only and Single User mode which it restores fine and leaves it in that mode. I have then been able to use the log shipping sp from this site and it has been doing that every hour and restoring the data fine. The system was set up to do a complete DB backup every night (the part I can not get working) I do not understand the log shipping 100% so can you tell me what consequences not doing the entire DB backup will bring if any?
I assume that stuff like changes in stored procedures will not be transferred with the log shipping.
Also after the log shipping procedure runs every hour it takes the database out of single user mode (even thought I have the EXEC sp_dboption ‘Fakename’, ‘single’, true
Line in there, but leave it in read-only mode. No one really uses this database it is just a backup, but if someone happens to be checking out the backup and the log shipping procedure runs will it have a problem if someone else is in there?

Its better to perform a full backup and restore periodically though you perform log shipping. If the database is too heavy in size then its better to rely on the LS process and maintain the time of interval between logs. If any changes occurs on the primary database, log shipping will transfer related transactions to secondary database without any doubt. If the secondary database is not used at all or even for MIS purposes then its better to leave it in single-user mode or in loading state this will ensure Log shipping to continue to restore the Transaction logs. To monitor the LS process take help of books online on Monitoring Log Shipping topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>