none
Error: 9001, Severity: 21, State: 1 error and Error: 9001, Severity: 21, State: 5

    Question

  • Hi all experts,

    I am facing one problem in sql express, "The log for database 'x' is not available", I also found solution to set AUTO CLOSE=OFF.

    But I want to know more about this. Is this error is related with deadlock?? (I'm seeing error just before above error like "was deadlock on locked resources with another process and has been chosen as the deadlock victim. Rerun the transaction")

    Please help!!!!! Any help appreciated. Thanks in advance

    Saturday, October 31, 2015 10:25 AM

Answers

All replies

  • select * from sys.databases where name='dbname'  => see database status

    and see error log also event log.anlysis error.

    Check if your database has auto_close enabled.


    select @@SERVERNAME AS server_name,getutcdate() as report_date_utc,name as database_name, is_auto_close_on , state_desc, user_access_desc
    from sys.databases
    where is_auto_close_on = 1
    order by name asc

    If autoclose is on, switch this database to no longer use auto_close.

    Before making any changes, check the integrity of the database. If there are no errors generated by this command, then  move on to changing the auto_close option.

    dbcc checkdb('database_name')

    alter database [database_name] set offline with rollback immediate;
    go
    alter database [database_name] set online;

    alter database [database_name] set AUTO_CLOSE OFF;


    Please click "Mark As Answer" if my post helped.



    • Edited by Av111 Saturday, October 31, 2015 10:45 AM
    Saturday, October 31, 2015 10:42 AM
  • Hi chaitanya,

    Thank you for posting in MSDN forum.

    Issue is not related to deadlock but please verify your event log for more information.

    Work around, Please set AUTO CLOSE=OFF and put database offline and online. It should solve your problem.

    Please have a look into very similar thread and some useful article for help,

    http://www.sqlservercentral.com/Forums/Topic1037190-1550-1.aspx 

    SQL Server Error: 9001, Severity: 21, State: 5 – The log for database `database_name` is not available. 

    Error: 9001, Severity: 21, State: 1 | The log for database ‘tempdb’ is not available


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Saturday, October 31, 2015 10:54 AM
  • Thanks, you are exactly right!!. But could  you please explain me why this happen?? I want to know the cause. What could be possible event occurs?? Please help me!!  
    Saturday, October 31, 2015 10:54 AM
  •  database had 'autoclose' option enabled and while trying to open that database it messed up with some system process and got killed. not sure what all happened there.  just disabled this option, took the database offline and back online and it fixed the issue.

    Please click "Mark As Answer" if my post helped.

    Saturday, October 31, 2015 11:39 AM
  • Hi,

    I have faced the same problem with one of my database. Even DBCC checkdb given the same error. I simply brought the database offline then online and it resolved the issue.

    Thanks.

    Monday, May 29, 2017 2:10 PM
  • I got this error when the managed service account that sql services were set to run under no longer had required user right "log on as a service".
    Wednesday, July 18, 2018 2:58 PM