Msg. 945, Database cannot be opened due to inaccessible files or insufficient memory or disk space.

Was doing an health check the other day and I ran into this while trying to list all database file details from the server:

   Msg 945, Level 14, state 2, Line 2
   Database XXX cannot be opened due to inaccessible files or insufficient memory 
   or disk space. See the SQL Server errorlog for details.

So I tried to access the database to see what the problem was -- no good.  The gui would not let me right click/properties, and then I tried to run this statement it failed w/the same error posted above:

     USE dbname

     EXEC sp_helpfile

So.  How do you get in there and look at your files when the server will not let you see them?  Easy sneasy.

    SELECT

       sd.name DBName,

       mf.type_desc FileType,

       physical_name Location

    FROM

       sys.master_files mf INNER JOIN sys.databases sd

         ON mf.database_id = sd.database_id

    ORDER BY

       sd.name

In this particular case, this was the output:

That N drive does not exist on the server.  Hence, the engine cannot find and open the file.  These are a couple other messages you may find in the errorlog in a situation like this:

Message  File activation failure. The physical file name "N:\XXXX\Data_100_1.LDF" may be incorrect.

Message  FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'N:\XXXX\Data_100_1.LDF'. Diagnose and correct the operating system Error, and retry the operation.

Little more detail on sys.master_files:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15

Correct orphaned users in SQL Server

What is an 'orphaned' user?  A database user for which the corresponding SQL Server login is missing, or incorrectly defined, is called an 'orphaned user' -- and, this user cannot login to the SQL Server instance.  This can happen for a few different reasons, but mostly when the database is restored/attached from a different instance, but the corresponding login was never created.

Typically you'll find this after you've restored a database and a user calls to tell you they cannot login... but, you can also look for them with this statement after doing a restore:

-- find orphaned users

SELECT

       dp.type_desc,

       dp.SID,

       dp.name [user_name]

FROM

       sys.database_principals dp LEFT JOIN sys.server_principals sp 

      ON dp.SID = sp.SID 

WHERE

       sp.SID IS NULL 

    AND authentication_type_desc = 'INSTANCE';  

Next step is to resolve that orphaned user.  To do that you will run this CREATE statement in the master database with the SID returned from the above:

        -- resolve orphaned user

        CREATE LOGIN login_name

        WITH PASSWORD = 'strong_password', 

        SID = 'SIDfromAbove'; 

OR -- if the login DOES exist in the master database, then you will just use this statement to map the orphaned user back to that login in the database:

        ALTER USER [user_name] WITH LOGIN = [login_name];

I have used that last ALTER numerous times after restoring a database from one server to another, where the login already exists in the master, but the users cannot login to the restored database(s).  

Hope to have helped!

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

Ran into a problem today with file system permissions, so I wanted to share with my readers.  Long story short, this code worked great when it was targeting a local drive on the server, as it is written here:

-- output new data to report file

DECLARE

  @dbColumnHeader VARCHAR(8000),

  @dbTable VARCHAR(500) ='GrowthStatistics',

  @dbCommand VARCHAR(1000),

  @dbFile VARCHAR(255)

SET @dbFile = 'D:\MSSQL\Reports\GrowthStatistics_' + CONVERT(char(8),GETDATE(),112) + '.csv'

SELECT @dbColumnHeader = COALESCE(@dbColumnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @dbTable

SELECT @dbCommand = 'bcp "SELECT '+ @dbColumnHeader +' UNION ALL  SELECT DatabaseName,CAST(OriginalSizeMB AS VARCHAR(15)) OriginalSizeMB,CAST(LastWeekMB AS VARCHAR(15)) LastWeekMB,CAST(NewSizeMB AS VARCHAR(15)) NewSizeMB,CAST(LargerMB AS VARCHAR(15)) LargerMB,[%Growth],[%Reduction] FROM DBA.dbo.GrowthStatistics" queryout '  + @dbFile + '  -SserverName -c -t, -T '

But, today I needed to change @dbFile to output the file to a UNC, like this:

   SET @dbFile = '\\share\path\Reports\GrowthStatistics_' + CONVERT(char(8),GETDATE(),112) + '.csv'

And it failed with both of these errors:

Msg 22051, Level 16, State 1, Line 0
Attachment file "\\share\path\Reports\GrowthStatistics_20200227.csv" is invalid.

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file NULL

Well, there are a number of different reasons for this, but it's basically the ability to read that UNC path... or inability, I should say. 😉 In my case, it was that the SQL Server service account did not have access to that new share we were trying to hit via the UNC path.

The Security Admin indicated that it was setup with the correct permissions when I checked, but it still did not work.  A bit of googling and I found a very clever and FAST way to verify SQL Server access to a directory in the file system somewhere.  Run this statement from the SQL Server, pointing the DIR to the path that you are trying to hit.  

    EXEC master..xp_cmdshell 'DIR \\share\path\Reports'

If it is an access problem, you will receive this message:

Went back to my Security admin w/this evidence and and he made another change... and now all is well.

Hope to have helped someone.

SERVERPROPERTY for SQL Server version and build details

Say you have a large inventory of servers, and you need to query them all to confirm version and see what service patching may be needed.  Well here you go.  This is a very simple query that you need to run only once on a CMS to target many servers at the same time.  

Simple statement: 

-- query serveroperty for version and build details

SELECT

  CASE

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'    

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'

     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'

     ELSE 'UNKNONWN' END MajorVersion,

   SERVERPROPERTY('ProductLevel') ProductLevel,

   SERVERPROPERTY('Edition') Edition,

   SERVERPROPERTY('ProductVersion') ProductVersion

ORDER BY 1;

Your output will be a little something like this:

Note, you don't need to put a @@SERVERNAME into your SELECT because the CMS will return it back to you from every server registered within the CMS.  If you haven't used the CMS before, please check out the link below.  Definitely makes life easier when you need to run the same statement across multiple servers.  

https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver15

A quick look at all database files -- location, size and freespace

This is a very fast one.  Just something I ran today to quickly review all database file details on the server.  Well, not necessarily all details, but the ones that I needed --- location, size and freespace for each.  Fast and easy --> 

IF OBJECT_ID('tempdb..##tempfiles') IS NOT NULL

DROP TABLE ##tempfiles;

CREATE TABLE ##temp

(

    DatabaseName sysname,

    FileName sysname,

    PhysicalFileName nvarchar(500),

    FileSize decimal (18,2),

    FreeSpace decimal (18,2)

)  

EXEC sp_msforeachdb '

USE [?];

INSERT ##temp (DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace)

SELECT DB_NAME(), Name, physical_name, CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) AS NVARCHAR) Size,

 CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS DECIMAL(18,2)) AS NVARCHAR) FreeSpace

FROM sys.database_files

'

SELECT

       DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace

FROM ##temp

DROP TABLE ##temp;

Sample output:

You either MUST ignore the C drive location of those, files, or please just remember that it is a bad idea to locate any of your databases on the C drive -- system or otherwise.

They are just there for a fast sample on this code.  Check it out and let me know what you think.

Happy SQL.