Database cannot be opened due to inaccessible files or insufficient memory or disk space

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Msg 945, Level 14, State 2, Line 1

Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Solution:

Check Microsoft SQL Server error log and determine reason for the cause.

If it is because of a persistent I/O error related to Application Programming Interface, a torn page, or other hardware issues, resolved it and restore it with help of backup. But if there is no availability of backups then try DBCC CHECKDB repair option.

 If possible add some disk space to files drive or cleanup some disk space.

Verify the permissions of user account.

Verify if the database is property is set to AutoGrow On.

.mdf and .ldf files should not be marked as Read Only on windows level.

If none of the above solved Issue: Try this

1. Change the database to offline to clear the db status

use master

alter database dbname set offline

2. Now change the database to online, at this step log file and data files will be verified by sql server

use master

alter database dbname set online

Is it is possible to Limit the number of ErrorLog Files in SQL Server

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

SELECT SERVERPROPERTY('ErrorLogFileName')

Is it possible to limit the number of errorlog files less then 6 ?

Nope. Minimum number to configure is 6. We can delete all the archived errorlog files from server if we want but  ensure they are not required for auditing purposes and such.

we can set it to 6 and then create a scheduled job that simply calls

EXEC sp_cycle_errorlog

The more often we run this, the smaller error log files will have and will keep it to 6 error log files.

 clean up message by reviewing what messages are appearing. For example if it is "successful backup" messages you can enable trace flag 3226 globally for that instance and this will suppress those messages from being written all the time. Outside of that it is up to us to determine what is writing the message and figure out how to clean that up. An example of this that is most common is "failed login" messages for rogue service or application on a remote server.pplication on a remote server.