Tuesday, March 31, 2020

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:


Tuesday, March 10, 2020

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!

Thursday, February 27, 2020

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.