Post reply

log for database 'my db' is not available

  • October 8, 2010 at 12:46 am

    #227523

    Hello, I have a strange problem since last 2days with two of my db's when the scheduled job ran shrink db & reindex database got an error box >> MicroSoft SQL-DMO (ODBC SQLState: HY000) Error 9001: The log for database 'my db' is not available. << Any ideas ... ?? I found nothing in MS web site or Tech Net. I run SQL 2000 sp4 on NT4 sp4.

    Thanks for your time.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • October 8, 2010 at 2:19 am

    #1234057

    First.Why are you running shrink?

    Second. Please check the SQL error log, look for any and all messages relating to that database.

    What state is the DB in at the moment? (look at state_desc in sys.databases for that database)

    Is the log file there? Look in Explorer.

    Do you have a backup of this database?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 8, 2010 at 3:04 am

    #1234080

    Those jobs are set on server, runs daily.

    Database state is online.

    All the log files are there.

    I got this error in early morning today for 2databases.

    Yesterday i got it only for one database.

    What would be a problem then?

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • October 8, 2010 at 3:58 am

    #1234108

    No idea. Not enough information

    Please check the SQL error log, look for any and all messages relating to that database.

    Did anything happen before that error? (bringing DB online, starting up SQL...)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 8, 2010 at 4:01 am

    #1234110

    Please paste the log or error msg with numbera


    - Pradyothana     http://www.msqlserver.com

  • October 8, 2010 at 10:17 pm

    #1234588

    Today also i got an error for one database while shrinking.

    Nothing is logged into error log.

    I saw this error in the maintainance plan history.

    -------------------------------------------------------------------

    [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 9001: [Microsoft][ODBC SQL Server Driver][SQL Server]The log for database 'DBName' is not available.

    -------------------------------------------------------------------

    Log file of this database is there on the HDD.

    Then what would be cause of an error.

    Pls reply.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • October 9, 2010 at 2:09 am

    #1234594

    Firstly why the hell are you shrinking the databases?

    Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Are you certain there's nothing in the error log? That kind of error is serious and SQL's not going to just ignore it.

    Are the DBs in autoclose?

    Is there a virus protector with incorrect file exclusions on that server?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 9, 2010 at 2:57 am

    #1234601

    All the sql jobs are set on the servers by our previous DBA.

    I read the article.

    Its really interesting.

    Ok.

    I will change schedule of shrinking to weekly rather than taking daily.

    Or i permanently delete those jobs.

    Shrinking job is merged with reindexing in maintainance plan.

    What should i do then.

    Thanks for help Gail.

    Pls reply.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

  • October 9, 2010 at 3:49 am

    #1234606

    Remove the shrink tasks completely. They are hurting your database.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 9, 2010 at 5:08 am

    #1234612

    Thanks Gail,

    I will try to work on that & will see the performance on Monday.

    [font="Verdana"]Regards,

    Pritam Salvi
    SQL DBA
    Mumbai. India[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

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