We have a couple of databases on 2 different MS SQL 2005 Express
servers that every week or so will be unaccessible. When we try to
access the database from SQL Management Studio we get an error 945. If
we run the code below is says the database is suspect. If we run
"alter database [database name] set offline" then "alter database
[database name] set online" the database will work again for a week or
so. If we run DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS there are no
errors with the database reported. Any ideas of what is going on?


SELECT DATABASEPROPERTYEX('Database Name', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'Database Name'
GO

Admin Matt (admin.matthew (AT) gmail (DOT) com) writes:
Message 945 reads "Database '%.*ls' cannot be opened due to inaccessible
files or insufficient memory or disk space. See the SQL Server errorlog for
details."

Have you checked the SQL Server errorlog for messages?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

The error logs show that the LDF file can't be accessed because it is
being used by another process (event id: 17207). This process is no
longer using the file by the time we can check out the problem. The
event id always appears at night. We are now having the antivirus
avoid scanning the LDF and MDF files and also we are turning off the
Backup Exec software for now with the Data folder even though the
errors normally appear before the backup even starts. Finally we are
setting the problem database Auto Close option to False. I will post
the results. It will probably take more than a week to know if the
issue is actually fixed.

On May 11, 1:16*pm, Admin Matt <admin.matt... (AT) gmail (DOT) com> wrote:
I had a go-around like this with my network administrator. Sometimes
and admin tool fails to handle an exceptions properly and leaves a
zombie proces. Our solution was for me to schedule nighttime windows
on the server which I keep clear of maintenance processes in which he
was free to schedule his tools. Then I do a prophylactic system
restart first thing in the the morning after. I realize not every
operation can do this, but it finishes off the zombies, and the
occaisional operation overrunning its time limit, with great
certainty.

Fred.

Admin Matt (admin.matthew (AT) gmail (DOT) com) writes:
I've no idea what might be looking at your log file, but turning off
autoclose should learn them to keep out.

Not that it is relevant to your, but I had some crappy backup software
running on my machine at home; it was something I got with the NAS. Fairly
often I would get errors about locked files. And this happened even in
directories that were not included in the backup scheme. Eventually I
got myself together and uninstalled.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 11 Mai, 19:16, Admin Matt <admin.matt... (AT) gmail (DOT) com> wrote:
Well, that is a good idea anyway - might improve performance as well.

You could install ProcessMonitor on the system with appropriate
filtering for LDF and MDF in "Path" field and probably file operations
(open, close). That should give you an idea who is accessing those
files.

Kind regards

robert

That is a good idea Robert, I might give that a try as well.

It appears setting the database Auto Close to False on both servers
worked. I hope this helps someone else in the future.