December 16, 2011 at 10:08 am
Members, I'm looking for some advice on the following scenario.
I have 2 servers, ServerA hosting the primary database, and ServerB with a log shipped copy of the database. Note that the log shipped version on ServerB has been restored and continually updated with the NORECOVERY option of the RESTORE DATABASE command for all subsequent log restores.
Log shipping was working fine until the SQL engine on ServerB (log shipped copy) was shutdown. In SSMS the database is visible and has the yellow database icon beside it, however, cannot be expanded in object explorer. Right-clicking and selecting properties in SSMS yields only the 'general' page, which is expected. Checking the sys.master_files table shows that the database is still in a RESTORING state, i.e. state = 1. However in the GUI, SSMS does not indicate this. I show only "DBNAME" under databases, when normally I usually see "DBNAME (Restoring...).
The issue here is that I'd like to resume log shipping as this database is quite large and I don't want to have to re-initiate the log shipping procedure (i.e. backup, copy, restore in norecovery, continue log shipping). Furthermore, I cannot put the db into RECOVERY mode as this will break the established log shipping (AFAIK) and will render future logs shipped useless.
I have tried the following without any success:
1. restore database <dbname> with norecovery << while this is successful, it doesn't allow me to send an additional log and resume log shipping, I receive the error "A previous restore operation was interrupted and did not complete processing on file '<dbname>'. Either restore the backup set that was interrupted or restart the restore sequence."
2. Following that advice, I attempted to restore the previously successfully applied log and the next log waiting to be applied, and received the same error as in #1 above.
3. Next I tried the following:
alter database <dbname> set emergency
but received the error: ALTER DATABASE is not permitted while a database is in the Restoring state.
So I'm at a state whereby I have no database to recover to on my secondary server. Members, is there a method whereby I can get this log shipped database back into a state whereby I can apply future log files to it? I'm thinking there has to be something as the engine still thinks the db is in a restoring state.
If anyone has any tips/tricks/hacks to get around this let me know.
Thanks.
December 16, 2011 at 11:02 am
did you try to redo the first unsuccessful log restore ? (the one that failed during your outage)
( or a restart of it )
I haven't had this kind of issue and have currently no replication implemented and haven't got the test infra at my disposal to test you scenario right now :blink:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 11:23 am
ALZDBA (12/16/2011)
did you try to redo the first unsuccessful log restore ? (the one that failed during your outage)( or a restart of it )
I haven't had this kind of issue and have currently no replication implemented and haven't got the test infra at my disposal to test you scenario right now :blink:
Yes, I've attempted to redo the log restores, but a restore was not happening during the outage.
I've done more digging and see this in the error log:
2011-12-16 12:58:06.890 spid13s Starting up database 'msdb'.
2011-12-16 12:58:06.890 Server Dedicated admin connection support was established for listening locally on port 1434.
2011-12-16 12:58:06.890 spid14s Starting up database '<db1>'.
2011-12-16 12:58:06.900 spid15s Starting up database '<db2>'.
2011-12-16 12:58:06.900 spid16s Starting up database '<db3>'.
2011-12-16 12:58:06.900 spid14s Error: 15581, Severity: 16, State: 3.
2011-12-16 12:58:06.900 spid14s Please create a master key in the database or open the master key in the session before performing this operation.
2011-12-16 12:58:06.900 spid15s Error: 15581, Severity: 16, State: 3.
2011-12-16 12:58:06.900 spid15s Please create a master key in the database or open the master key in the session before performing this operation.
2011-12-16 12:58:06.910 spid16s Error: 15581, Severity: 16, State: 3.
2011-12-16 12:58:06.910 spid16s Please create a master key in the database or open the master key in the session before performing this operation.
2011-12-16 12:58:06.970 spid10s Clearing tempdb database.
2011-12-16 12:58:07.030 spid10s Starting up database 'tempdb'.
which tells me that the master key needs to be open (for which it requires a password) before it can read the databases. I do have TDE on the log shipped server and all certificates/keys are restored properly.
Is there a method by which I can start SQL and open the master key with password prior to it attempting to start the databases? Perhaps at the command line level with a switch/trace flag or something?
I understand the engine has no idea what the password is, so I need to 'tell it', but don't know how to do this prior to SQL engine start up.
Thanks.
December 16, 2011 at 2:14 pm
This is out of my league at this moment.
I've launched a tweet using #sqlhelp and hope people will see and hop in.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 2:42 pm
Update: here is something else that came to light in the environment. Upon shutting down SQL via the configuration tool, the SQL service is sitting in a 'stopping' state.
Further investigation via dbcc opentran('master') shows the following open transaction
Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID): 5s
UID (user ID) : -1
Name : Update SMK Encryption
LSN : (973:272:1)
Start time : Dec 16 2011 3:31:30:173PM
SID : 0x0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Notice the SPID, 5s and the Name of the transaction. Something in the background (system process) is updating the encryption and not letting go of whatever lock is happening. Furthermore, I have reviewed all the service pack fix logs post the version I'm running, and nothing appears to address this issue.
I'm hoping someone will see this and has experienced something like this before and was able to resolve.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply