1

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:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf" may be incorrect.
File activation failure. The physical file name "C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_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:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB.mdf          3 
TestDB       TestDB_log   C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log.ldf      3
TestDB       TestDB-2_log C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca\TestDB_log-2.ldf    3
  • To the server, the state of the DB IS online. This is a very strange request - what's the context of why you need this? Why do mountpoints drop out? – George.Palacios Aug 23 '18 at 15:44
  • @George.Palacios hi. context ? what do you mean ? sometimes it happens unfortunately but this is the other story. – isxaker Aug 23 '18 at 16:40
  • You can use xp_cmdshell 'type NUL > 1.txt' to your mount path `C:\MOUNT\b4c059e8-3ba6-425f-9a2a-f1713e7719ca` . if it is success then go ahead with your restore. BTW, putting database files on C:\ drive (system drive) is not a good idea. – Kin Shah Aug 23 '18 at 18:38
  • @kin that's a separate volume mounted in a directory under c:\. So not actually on the system drive. – David Browne - Microsoft Aug 23 '18 at 19:34
  • But which credentials should i use for checking the db files ? I was thinking about checking files as a possible solution. But sql server can be started under different credentials. So there're some cases when it doesn't work(or at least you need specific credentials to the host) – isxaker Aug 23 '18 at 19:57
1

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.

  • Thank for your advise. But under what credentials should the PS be launched? I was thinking about checking files as a possible solution. But sql server can be started under different credentials. So there're some cases when it doesn't work(or at least you need specific credentials to the host) – isxaker Aug 23 '18 at 19:53
0

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:\MOUNT\f99bae7b-d2b0-45de-94f4-064ad8d19186\TestDB.mdf', @result OUTPUT
select @result as exist

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.