Incorrect syntax near '(' - sys.dm_db_index_physical_stats

So I have this script that I run often to check index fragmentation percentage with SQL Server tables.  I've run it a million times without error, but suddenly today it fails with this message:

     Msg 102, Level 15, State 1, Line 24
      Incorrect syntax near '('.

Even weirder, is that I ran it successfully on one of the databases on the server, but it failed for every other one I tried -- same server.  Very odd. 

SELECT

       @@SERVERNAME ServerName,

       DB_NAME() DatabaseName,

       dbs.[name] [Schema],

       dbtables.[name] [Table],

       dbi.[name] [Index],

       idx.avg_fragmentation_in_percent [%Fragmented],

       idx.page_count [#Pages]

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) idx

       INNER JOIN sys.tables dbtables

         ON dbtables.[object_id] = idx.[object_id] INNER JOIN sys.schemas dbs

           ON dbtables.[schema_id] = dbs.[schema_id] INNER JOIN sys.indexes dbi

                ON dbi.[object_id] = idx.[object_id]

                AND idx.index_id = dbi.index_id

WHERE

       idx.database_id = DB_ID()

       AND idx.avg_fragmentation_in_percent  > 30

       AND idx.page_count > 1000

ORDER BY

       idx.avg_fragmentation_in_percent DESC,

       idx.page_count DESC

A lot of googling, and I found that it was because due to a conflicting compatibility level.  My server was 2012, but the database compatibility level was 2000... which does not use the DB_ID() parameter.  My workaround was to use a variable for the @db_id instead, like this:

USE dbname

DECLARE @db_id INT

SELECT @db_id = DB_ID()

SELECT

       @@SERVERNAME ServerName,

       DB_NAME() DatabaseName,

       dbs.[name] [Schema],

       dbtables.[name] [Table],

       dbi.[name] [Index],

       idx.avg_fragmentation_in_percent [%Fragmented],

       idx.page_count [#Pages]

FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL, NULL, NULL) idx

       INNER JOIN sys.tables dbtables

         ON dbtables.[object_id] = idx.[object_id] INNER JOIN sys.schemas dbs

           ON dbtables.[schema_id] = dbs.[schema_id] INNER JOIN sys.indexes dbi

                ON dbi.[object_id] = idx.[object_id]

                AND idx.index_id = dbi.index_id

WHERE

       idx.database_id = DB_ID()

       AND idx.avg_fragmentation_in_percent  > 30

       AND idx.page_count > 1000

ORDER BY

       idx.avg_fragmentation_in_percent DESC,

       idx.page_count DESC

Completed successfully this time...

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!