April 21, 2009 at 3:58 am
Dear Friends,
I am getting problem with my database(mentioned below), please give me the solution ASAP. because my production server has stoped. and I am not getting what to do. and in client side all are putting too much pressure on me.
This is Error messsage.
[font="Verdana"]
"Database 'COMMON' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."[/font]
Thankis in Advance
Sharanu.
April 21, 2009 at 4:57 am
- Check the disk space where the .mdf .ndf or ldf files are located.
- If the disk space is full due .ldf
detach the database and add new disk
move the files there and attach the database
- This is a offline operation
Sat
Cheer Satish 🙂
April 21, 2009 at 5:04 am
seems this is a memory problem.
please provide some more detail like size of your mdf,ldf and location of both.
configuration available.
if space is not a problem check integrity of your database.
update if you are still facing problem.
Manoj
April 21, 2009 at 5:19 am
What does the SQL error log say? There will be a much more detailed error there. Please check the error log and post any errors relating to database Common.
This is not something that can be trivially fixed without knowing the cause.
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
April 21, 2009 at 5:21 am
TECHBABU (4/21/2009)
- Check the disk space where the .mdf .ndf or ldf files are located.- If the disk space is full due .ldf
detach the database and add new disk
move the files there and attach the database
- This is a offline operation
I would strongly suggest not detaching the database immediately. Depending on what the root cause is, it may happen that you detach the database and it will not reattach again.
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
April 21, 2009 at 6:53 am
Dear Friends,
Thanks for your valuable response,
here i am giving some more details,
my database is located at D drive and it is having 22GB space,
and mainly effected DB is common, its .mdf is 100MB and .ldf is 450MB, and
when i open the SQL server management studio and I suppose to query as
"Use common" then I am getting this problem "Database 'COMMON' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.", and my applications user name and password are stored in this DB so i am not able to access the application. and beside in Object explore I am not getting +sign to explode that database, its something locked.
please give me the solution so that my application works properly.
Thanks
Sharanu.
April 21, 2009 at 7:08 am
Check the available space on your C drive, especially if the tempdb is stored on there.
Oh and if your mdf file is only 100Mb, you shouldn't have a log of 450Mb. Is the "Common" db in full mode?
April 21, 2009 at 7:31 am
sharanurampur (4/21/2009)
please give me the solution so that my application works properly.
Have a look through the SQL error log. There will be more details there regarding the cause of the problem. Find those errors and post them here.
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
April 21, 2009 at 7:56 am
when I checked into the log files i got some message like..
2009-04-21 14:28:24.96 spid27s Starting up database 'DUMMY'.
2009-04-21 14:28:26.84 spid4s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2009-04-21 14:28:37.32 spid9s Starting up database 'tempdb'.
2009-04-21 14:28:38.59 spid4s Recovery is complete. This is an informational message only. No user action is required.
2009-04-21 14:28:38.59 spid12s The Service Broker protocol transport is disabled or not configured.
2009-04-21 14:28:38.59 spid12s The Database Mirroring protocol transport is disabled or not configured.
2009-04-21 14:28:38.76 spid12s Service Broker manager has started.
2009-04-21 14:28:45.76 spid51 Using 'xpsqlbot.dll' version '2005.90.3042' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2009-04-21 14:28:45.98 spid51 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2009-04-21 14:28:46.12 spid51 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2009-04-21 14:41:04.84 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1712, committed (KB): 74716, memory utilization: 2%.
2009-04-21 14:46:36.79 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 331 seconds. Working set (KB): 16892, committed (KB): 39140, memory utilization: 43%.
2009-04-21 14:51:08.57 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 603 seconds. Working set (KB): 15104, committed (KB): 36524, memory utilization: 41%.
2009-04-21 14:56:35.23 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 930 seconds. Working set (KB): 17212, committed (KB): 38988, memory utilization: 44%.
2009-04-21 15:01:07.17 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1202 seconds. Working set (KB): 15148, committed (KB): 36524, memory utilization: 41%.
2009-04-21 15:06:33.90 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1529 seconds. Working set (KB): 17200, committed (KB): 38708, memory utilization: 44%.
2009-04-21 15:11:05.84 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1801 seconds. Working set (KB): 15164, committed (KB): 36516, memory utilization: 41%.
2009-04-21 15:42:02.53 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3657 seconds. Working set (KB): 17276, committed (KB): 38876, memory utilization: 44%.
2009-04-21 16:41:54.65 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7249 seconds. Working set (KB): 8276, committed (KB): 38900, memory utilization: 21%.
2009-04-21 18:41:38.96 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14434 seconds. Working set (KB): 10540, committed (KB): 38756, memory utilization: 27%.
April 21, 2009 at 10:09 am
Can you access the D drive on the server? Can you navigate to the folder where the mdf/ndf/ldf files exist and verify that they do in fact exist?
I have seen similar messages when there has been a network outage to the SAN. The outage was only for a second, but SQL Server lost the connection and could no longer access the drive. The only solution for this scenario was to reboot the server. I am not saying this is your problem - so don't just go and reboot the server to try and solve this problem (yet).
The errors you are getting refer to problems with memory. Are you running SQL Server 2005 Standard? Is it x64 or x86? What is the max memory setting for this instance? Are you running multiple instances on this system?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 1:31 pm
sharanurampur (4/21/2009)
when I checked into the log files i got some message like..
No errors there. Are there any messages in the log that relate to the database Common?
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
April 21, 2009 at 3:35 pm
sharanurampur (4/21/2009)
my database is located at D drive and it is having 22GB space,
and mainly effected DB is common, its .mdf is 100MB and .ldf is 450MB,
Yes, But do you have free space on the disk? What all other files are located on your D drive
"Database 'COMMON' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.",
If you assure its not related to disk space then as the error suggest this might be a problem with your Memory on the server? What is the total memory on your server? Can you give us more details on this? and also your SQL server specifications?
Thanks,
April 22, 2009 at 12:34 am
Hello Sir,
ours is SQL server standard edition and we are using only one instance cause it is production server and it is x86.and how to check how much memory is allocated to this instance.
Thanks
Sharanu.
April 22, 2009 at 12:56 am
SQL Server Performance Monitor -> add counter Total Server Memory(in KB)
will display how much memory is allocated by an instance of SQL Server
Memory-Available Bytes
will display how many bytes of memory are currently available for use by processes.
Memory-Pages/sec
will display no of pages in and out to disk. if it is high excessive paging.
April 22, 2009 at 5:36 am
sharanurampur (4/21/2009)
[font="Verdana"]"Database 'COMMON' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."[/font]
Thankis in Advance
Sharanu.
Have you verified whether data and log files for database that you are having issue is accessible from drive 'D' as mentioned by Williams.
Did this happen after restarting sql server? If it is the case, I am sure this command should help you out
alter database COMMON set offline
alter database COMMON set online
Note:- Try this command only if the database did not come up after sql server restart.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply