Post reply

model database stuck in restore state

  • July 28, 2011 at 9:05 am

    #1360195

    Ignacio A. Salom Rangel (7/28/2011)

    Ninja's_RGR'us (7/28/2011)

    brown-415622 (7/28/2011)

    Thanks I will try this, is it ok to try this during production hours?

    I have never seen / tested that scenario and I don't intend to try it on my prod server ;-).

    I tried this on my test environment before and it should not be a problem to run the restore script!

    I was reffering to putting model into norecovery and THEN restarting the server or making a new DB. 😀

  • July 28, 2011 at 9:17 am

    #1360210

    Yes

  • July 28, 2011 at 9:20 am

    #1360219

    brown-415622 (7/28/2011)

    Yes

    Yes to what??

  • July 28, 2011 at 9:33 am

    #1360234

    The script worked guys thank you very much. I really appreciate it.

    If your curious the results of the query were

    "RESTORE DATABASE successfully processed 0 pages in 4.004 seconds (0.000 MB/sec)

  • July 28, 2011 at 9:34 am

    #1360235

    brown-415622 (7/28/2011)

    The script worked guys thank you very much. I really appreciate it.

    If your curious the results of the query were

    "RESTORE DATABASE successfully processed 0 pages in 4.004 seconds (0.000 MB/sec)

    Thanks for sharing!



    My blog

  • July 28, 2011 at 9:35 am

    #1360236

    Ninja's_RGR'us (7/28/2011)

    I was reffering to putting model into norecovery and THEN restarting the server or making a new DB. 😀

    Ok, I misundersood! 😀



    My blog

  • July 28, 2011 at 9:40 am

    #1360239

    brown-415622 (7/28/2011)

    The script worked guys thank you very much. I really appreciate it.

    If your curious the results of the query were

    "RESTORE DATABASE successfully processed 0 pages in 4.004 seconds (0.000 MB/sec)

    Restore database outputs those message. Since your command just mean PUT IT ONLINE. It didn't process anything (0 pages, 0 MB...).

    Here's what it looks like when you actually do a real restore (pardon my french !)

    25520 pages traitées pour la base de données 'PROD-FORDIA_test_restore', fichier 'PROD-FORDIA_Data' dans le fichier 1. [SQLSTATE 01000]

    2587352 pages traitées pour la base de données 'PROD-FORDIA_test_restore', fichier 'PROD-FORDIA_1_Data' dans le fichier 1. [SQLSTATE 01000]

    2 pages traitées pour la base de données 'PROD-FORDIA_test_restore', fichier 'PROD-FORDIA_Log' dans le fichier 1. [SQLSTATE 01000]

    RESTORE DATABASE a traité avec succès 2612874 pages en 377.411 secondes (56.714 Mo/s). [SQLSTATE 01000]

  • July 28, 2011 at 9:41 am

    #1360240

    brown-415622 (7/28/2011)

    The script worked guys thank you very much. I really appreciate it.

    If your curious the results of the query were

    "RESTORE DATABASE successfully processed 0 pages in 4.004 seconds (0.000 MB/sec)

    PS Check to see who changed the state of the DB... just to make sure it doesn't happen again!

  • July 28, 2011 at 10:29 am

    #1360303

    You are correct, I thought it worked because I didn't see the restore status showing in the gui. WHen I tried to look at the some views in the database

    it still says its restoring. I checked to see if I had any backups of the model database and I don't have any I believe my maintenance clean up plan deleted the last one I had.

    Can I do a repair from the SQL Server CD or restore from a development box.

  • July 28, 2011 at 10:34 am

    #1360311

    I've seen 3rd party apps do crazy things but that would take the cake.

    Can you post the logs content plz?

  • July 28, 2011 at 10:47 am

    #1360323

    Attached is one of the logs

  • July 28, 2011 at 10:52 am

    #1360328

    You seem to have a 3rd party app take takes all your backups. It's been failing for days.

    Please run this and post back what it returns :

    DBCC CHECKDB('model') WITH ALL_ERRORMSGS , NO_INFOMSGS

    Your problems seem to have started quite a few days ago. PS why did you restart on the 22nd?

    011-07-22 07:53:27.23 spid106 CHECKDB for database 'Genesis_Train' finished without errors on 2011-07-15 00:42:10.873 (local time). This is an informational message only; no user action is required.

    2011-07-22 07:53:27.23 Backup Restore is complete on database 'Genesis_Train'. The database is now available.

    2011-07-22 07:53:27.23 Backup Database was restored: Database: Genesis_Train, creation date(time): 2009/01/26(12:17:06), first LSN: 3463902:82570:13, last LSN: 3463902:82577:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\sql backup\Genesis_Train\Train_to_Local'}). Informational message. No user action required.

    2011-07-22 09:35:21.52 Backup Error: 3041, Severity: 16, State: 1.

    2011-07-22 09:35:21.52 Backup BACKUP failed to complete the command BACKUP LOG Genesis. Check the backup application log for detailed messages.

    2011-07-22 09:35:21.62 Backup Error: 3041, Severity: 16, State: 1.

    2011-07-22 09:35:21.62 Backup BACKUP failed to complete the command BACKUP LOG model. Check the backup application log for detailed messages.

    2011-07-22 09:40:21.25 Backup Error: 3041, Severity: 16, State: 1.

  • July 28, 2011 at 10:59 am

    #1360337

    We have a in house programmer that supports the third party app and he did some software install on the 22nd assuming he must

    have rebooted. Here is what I got from the query

    Msg 927, Level 14, State 2, Line 1

    Database 'model' cannot be opened. It is in the middle of a restore.

  • July 28, 2011 at 11:04 am

    #1360346

    Suresh B. (7/28/2011)

    Not sure about the cause. But this my bring it online.

    restore database model with recovery

    rerun that and immediately run the check db commande I gave you.

    I'm 99.99% sure that the maintenance job is doing this to your dbs (more than 1)

  • July 28, 2011 at 11:22 am

    #1360362

    I ran both scripts and both completed successfully. It brought back the model database back online and was able to see

    views and tables after about 5 min it went back into restore mode. Im backing up the transaction log every 5min in the maintenance plan, you think that

    could be causing it?

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply