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

Available Protocols for SQL Server

Three Available Protocols for SQL Server

Shared Memory: This is an in-memory protocol and thus is only suitable for applications running
on the same machine as the SQL Server.

Named Pipes: This is an interprocess communications protocol (IPC) that enables a process to
communicate with another process, possibly running on a different computer,
through the use of shared memory. This protocol typically works well in small
and fast local area networks because it generates additional network traffi c
during use. In larger and slower networks, TCP/IP works better.

TCP/IP:  Transmission Control Protocol/Internet Protocol (TCP/IP), is widely used today.
TCP guarantees the delivery and order of the information sent between computers,
while IP defi nes the format or structure of the data sent. TCP/IP also
contains advanced security features that make it attractive to security-sensitive
organizations and users. This protocol works well in larger networks and slower

SQl Server "How is My Memory Being used" Performance Counters"

Reference: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

SQL Server 2008R2 Memory usage is High

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.
This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).
These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).
Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000
If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):
Physical RAM Target Avail RAM in Task Manager
< 4GB 512MB – 1GB
4-32GB 1GB – 2GB
32-128GB 2GB – 4GB
> 128GB > 4GB
You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

-- Turn on advanced optionsEXEC sp_configure'Show Advanced Options',1;GO
RECONFIGURE;GO-- Set max server memory = 3500MB for the serverEXEC sp_configure'max server memory (MB)',3500;GO
RECONFIGURE;GO

-- See what the current values are
EXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

Author:Glenn Berry
http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/



That's normal and by design.
SQL tries to keep everything in memory in order to better serve multiple requests for the same data.

Unless wetell it otherwise, SQL uses all the memory available.

On an 8 gig machine, we probably want to limit SQL to 7 gig, leaving 1 gig for the operating system. if we have other processes on that server besides SQL, you might want to limit SQL even more to accompdate some memory for those other processes.