How to get to know that db files are inaccessible?

Posted on

Question :

Let me explain my case.

I have a mount with database files on my local PC. I attach the database (from this mount). And then close this mount (in other word i attach db and then delete db files). So I have the database without files. And any ALTER query gives me something like this:

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

Basically I need to detach a database but in this situation this database can only be deleted(or repaired but it is not my case). Dropping database works perfectly:

DROP DATABASE [TestTableDatabase2005]

But my goal is get to know which state does this database have. And here I have problem. Query:

SELECT state 
FROM sys.databases 
WHERE name = N'TestDB'

Gives me ONLINE (it’s not true). But as soon as I execute for example this script(of course it finishes with error):

ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Msg 5120, Level 16, State 101, Line 10
Unable to open the physical file "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
File activation failure. The physical file name "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log.ldf" may be incorrect.
File activation failure. The physical file name "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log-2.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 10
Could not restart database "TestDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.

the state of my ‘TestDB’ will be changed on RECOVERY_PENDING.

Do you have any idea how I can check are these db files inaccessible or not?
Or may be i have an opportunity to refresh sql info about this db ?

P.S.
Following query says that files have actual size.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = N'TestDB'

DatabaseName Logical_Name Physical_Name                                                     SizeMB
------------ ------------ ----------------------------------------------------------------- -------
TestDB       TestDB       C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB.mdf          3 
TestDB       TestDB_log   C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log.ldf      3
TestDB       TestDB-2_log C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log-2.ldf    3

Answer :

If your file system is dropping out, that is a major problem. What if your database is partway through committing a transaction? This could cause corruption and potential data loss. This would be my first reaction.

If this is an issue in your test system only, and you have verified that the underlying file system fault is localized (IE definitely not affecting production systems) my feeling is that a SQL Agent job with step 1 configured to check for the physical files on disk could do the job.

Step 1 could consist of something like the following powershell:

if (Test-Path $path) {
   Return
} else {
   Throw "File doesn't exist"
}

The above code will throw an error if the file in the $path variable doesn’t exist.

Step 1 on failure would move to step 2, but on success would quit the job reporting success. Step 2 would then be whatever action you wish to take.

Finally, I’ve found what i actually need.
I can chech whether the specific file exists or not by sql server:

DECLARE @result INT
EXEC master.dbo.xp_fileexist 'C:MOUNTf99bae7b-d2b0-45de-94f4-064ad8d19186TestDB.mdf', @result OUTPUT
select @result as exist

exist
-----------
1

Leave a Reply

Your email address will not be published. Required fields are marked *