November 24, 2011 at 7:35 am
Hi,
I have a logshipped DB that has gone into Suspect mode. This isn't a huge problem for me because i have a full backup from the source DB that i can use to set it all up again, but i thought it would be an interesting exercise to see if i could recover it.
Looking in the SQL Server error log, the last few lines i can see relating to this database are:
Starting up database 'OpenTrader'.
CHECKDB for database 'OpenTrader' finished without errors on 2011-11-20 01:30:08.910 (local time). This is an informational message only; no user action is required.
Log was restored. Database: OpenTrader, creation date(time): 2009/09/06(15:51:22), first LSN: 324437:22737:1, last LSN: 324473:32804:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Backups\logshipping\OpenTrader_20111124013004.trn'}). This is an informational message. No user action is required.
Starting up database 'OpenTrader'.
The database 'OpenTrader' is marked RESTORING and is in a state that does not allow recovery to be run.
Recovery is writing a checkpoint in database 'OpenTrader' (22). This is an informational message only. No user action is required.
Running DBCC CHECKDB ('OpenTrader') WITH NO_INFOMSGS, ALL_ERRORMSGS yields:
Msg 927, Level 14, State 2, Line 1
Database 'OpenTrader' cannot be opened. It is in the middle of a restore.
There are currently no sessions trying to restore anything to this database according to the activity monitor and there are no results from the following query for the database concerned.
SELECT
DB_NAME(er.database_id) 'Database',
session_id 'SPID',
blocking_session_id 'Blocking_SPID',
wait_type,
wait_time,
wait_resource,
start_time 'Start_Time',
total_elapsed_time,
er.status,
command,
su.name,
[text] 'Query_Text',
query_plan 'Query_Plan'
FROM
sys.dm_exec_requests er
JOIN sys.database_principals dp ON dp.principal_id = er.user_id
JOIN sys.sysusers su ON dp.sid = su.sid
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE
er.session_id <> @@SPID
Currently the DB has a status of Standby/Suspect and i'm at a bit of a loss as to how to proceed, aside from restoring from full backup.
Any help would be greatly appreciated.
Simon
November 24, 2011 at 8:11 am
Have you received any errors along the following lines?
MSSQLERVER(14221 - Server): The log shipping secondary database SERVERNAME.databasename has restore threshold of x minutes and is out of sync. No restore was performed for y minutes. Restored latency is z minutes. Check agent log and logshipping monitor information.
There's more information here -> http://msdn.microsoft.com/en-us/library/aa337265.aspx
This is the normal symptom of a broken log shipping connection.
Also when you say suspect do you mean standby? Because your post makes the DB sound like it's restoring...
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 24, 2011 at 8:16 am
Hi,
Well it's not your regular run of the mill logshipping i'm afraid. Essentially, logs are shipped from a live environment to a DMZ, the files are copied using robocopy from the DMZ to another server, and then there is a custom written restore job that restores the files from there. It has all been working fine for months then all of a sudden today it's gone into this Standby/Suspect mode as per the screenshot attached.
Thanks,
Simon
November 24, 2011 at 8:21 am
Can you run:
EXEC SP_HELP_LOG_SHIPPING_MONITOR_PRIMARY @primary_server = 'servername' @primary_database = 'databasename'
GO
EXEC SP_HELP_LOG_SHIPPING_MONITOR_SECONDARY @secondary_server = 'servername' @secondary_database = 'databasename'
GO
This should return the log shipping configuration as SQL sees it.
Using Robocopy as the 'ferryman' is rather inventive btw!
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 24, 2011 at 8:29 am
Just thinking that maybe the restore threshold value was met on the primary server due to some sort of network interruption. The SSMS screen where restore thresholds are set (and also where you define in what mode the secondary will run - standby or restoring) might provide a clue if you drill down the current t/log settings.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 24, 2011 at 9:01 am
Hi,
I'm not using the normal sql server logshipping setup so your queries return no results. This is a custom solution to get around a restriction whereby our support server isn't allowed direct access to the source database, however the same source db is logshipped to our DR site and that is working fine.
Thanks,
Simon
November 24, 2011 at 9:06 am
I would just restore from backup. It's generally the recommended route when a database is suspect anyway. Emergency mode repairs are for those cases where a DB has gone suspect and there are no backups.
Make sure you do some hardware diagnostics. Corruption (and that's the main cause of a DB being suspect) is most of the time a hardware problem, generally IO subsystem. Check your logs, run some diagnostics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2011 at 9:09 am
Ok cheers Gail. I'll do that. Thanks for your help guys.
Thanks,
Simon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply