Post reply

Database(Recovering)

  • March 26, 2004 at 8:23 am

    #84059

    I have multiple servers in the SQL Server Group in Enterprise Manager on my local machine.  When I went in to update some data in the one database that I care about that is located on a network server, instead of seeing 'Diagrams, Tables, Views...' listed for that database, I saw dbname (Recovering), and (no items).  All of the other databases on that server displayed normally.

    Once my coworkers revived me after the shock, I went to the actual server itself, opened up Enterprise Manager, and lo and behold, there was the view of the db that I usually see.  I went back to my local Enterprise Manager session, pressed 'Refresh', and up popped the usual view.

    We don't have a true DBA here, and the other programmers here told me they've seen this happen from time to time.  I'd love to hear 'oh yeah, that's just another Enterprise Manager issue'.  So is it that, or is this a symptom of a more serious problem that I should be looking into?  And if so, what should I be looking for, and at?

    The db in question is SQL 2K.  All of our SQL Server and Enterprise Manager installations are straight out of the box, all defaults accepted, plain vanilla.

    Thanks for any help.

    Mattie(Recovering)

  • March 26, 2004 at 8:40 am

    #500674

    It is possible that it is  an issue of EM. However I would check the SQL Log of that server to be sure.

    Sometimes it is necesary to close and opened EM, because refresing it is not enough.

  • March 26, 2004 at 8:40 am

    #500673

    Shot in the dark...  Was this SQL Server recently starting up? SQL Server performs an automatic recovery at startup to write committed transactions into the database. This automatic recovery records actions on the database that may have been left in cache memory when SQL Server stopped. SQL Server’s two-phase commit model guarantees that transactions are recorded in the database only when they are completed successfully and a log is created. Although this model inhibits data corruption, it also means that when the database is terminated abnormally, you might lose work and wait through a long recovery period.  You can check the log to determine the last startup time.

    Do you have automated backups for your databases?  Both full and transaction backups?

    Francis

  • March 26, 2004 at 10:07 am

    #500694

    Well, seeing as autoclose was set to 'on' for this database (as well as every other database on the server), and this is currently just a test database with not much activity, it's not particularly surprising that yes, the database was just starting up.  So I've set the option to 'off', and will see if this problem recurs. 

    Now it's possible that there were uncommitted transactions.  Is there a way to check the transaction log to see when the transactions applied yesterday afternoon were actually committed?

    And as to your question about backups, there hadn't been any, but even though it's just test, we decided to do a daily database backup.  Until it goes live, we're not going to bother with transaction log backups.

    Thanks so much for your input.

  • March 26, 2004 at 10:13 am

    #500698

    Re: Checking the transaction log for when tranactions got committed.... Hm perhaps with a third party tool like ApexSQL log...

    Francis

  • March 26, 2004 at 2:10 pm

    #500755

    Umm, if you're not bothering with transaction log backups, then your recovery model should be simple.  In this case there will be no committed transactions in the log to look at.  If your recovery model is full, then you should be taking transaction log backups.  Otherwise, the log won't be truncated, and will continue to grow, possibly filling the disk and crashing the database... unless of course, you have auto grow turned off, in which case the database will crash when the log fills up.  Full database backups don't truncate the log.

    Steve

     

  • March 26, 2004 at 2:27 pm

    #500760

    As luck would have it, the recovery model is simple.  However, once we go live, we will include transaction log backups.  But before that happens, I will have read up on all of the topics illuminated by everyone's comments on this post.

    You'd think I'd be embarrassed about how little I know on this topic, but mostly I'm just really grateful.  Thanks to everyone for their input.

    Mattie

  • March 26, 2004 at 2:30 pm

    #500761

    Hey, Mattie,

    At some point, none of us knew ANYTHING about it!

    Steve

Viewing 8 posts - 1 through 7 (of 7 total)

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