SQL Server 数据库备份9001错误：The log for database 'xxx' is not avai...

她人

SQL Server 数据库备份9001错误：The log for database 'xxx' is not available. Check the event log for related error messages.
备份数据库的时候有一个DB出现备份错误，其他DB都正常。这是一个奇怪的问题，因为这个DB配置上跟其他DB也没有什么异同。下面是错误信息：
由于安全考虑，有关server和DB都用xxx代替。

===================================

===================================

Backup failed for Server 'xxx'. (Microsoft.SqlServer.SmoExtended)

------------------------------
For help, click: http://go.microsoft.com/fwlink?P ... odVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

===================================

The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?P ... 01&LinkId=20476

------------------------------
Server Name: xxx
Error Number: 9001
Severity: 21
State: 1
Line Number: 1


------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

===================================




dbcc checkdb 的错误信息：
-----------------checkdb(xxx)-----------------

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 9001, Level 21, State 1, Line 1
The log for database 'xxx' is not available. Check the event log for related error messages. Resolve any errors and restart the database.






解决方案：
----fixed--

It is possible that the database was set to AutoClose, or was set OFFLINE, or while the SQL Server service wasn't running, an .ldf file was deleted or corrupted/compressed by other people or tools.

Make a copy of the MDF file and you can try to attach it using:

CREATE DATABASE mydb_copy ON (filename = 'C:\path to.mdf')
FOR ATTACH_REBUILD_LOG;

Take the database offline.
Bring it back online.
Run DBCC checkdb to make sure everything is good.
Set Auto Close = False.
刚开始以为会是权限问题，后来发现其他DB能备份成功，就不是这个问题了。
根据具体情况，我估计原因在于AutoClose。这个DB的AutoClose选项是TRUE，当DB为OFFLINE时，正好发生BACKUP操作，才会出现以上的错误信息。而当DB恢复为ONLINE时，由于错误信息一直未消除或者理解为DB没有正常的ONLINE导致无法正常备份。

关于AutoClose的一些知识点：

AUTO_CLOSE

当设置为 ON 时，在数据库的最后一个用户退出，而且数据库中的所有过程都完成时，数据库将关闭并完全退出系统，从而释放所有资源。默认情况下，当使用 Microsoft® SQL Server™ 2000 Desktop Engine (MSDE 2000) 时，对于所有数据库，此选项都设置为 ON，而对于所有其它版本都设置为 OFF，与操作系统无关。当用户试图再次使用数据库时，该数据库将自动重新打开。如果数据库完全退出系统，则该数据库不会重新打开，直到下一次 SQL Server 重新启动时，用户试图使用该数据库为止。当为 OFF 时，即使当前没有用户使用数据库，数据库仍然保持打开状态。

AUTO_CLOSE 选项对于桌面数据库很有用，因为它允许将数据库文件作为常规文件进行管理。它们可以移动、复制以制作备份，或者甚至通过电子邮件发送给其他用户。如果应用程序与 SQL Server 反复建立连接和断开连接，则不应对这样的应用程序所访问的数据库使用 AUTO_CLOSE 选项。在每个连接之间关闭和重新打开数据库的开销将削弱性能。

此选项的状态可以通过检查 DATABASEPROPERTYEX 函数的 IsAutoClose 属性来确定。

微软官方：

This rule checks whether the AUTO_ CLOSE option is set OFF. When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.
Best Practices Recommendations
If a database is accessed frequently, set the AUTO_CLOSE option to OFF for the database.

对于这个值其实是sqlserver2000的时候用得比较多，默认值是TRUE。而在sqlserver2005之后，默认值是FALSE。
所以，建议将AutoClose值设为FALSE。