Sql-server – Does having a .ldf for a index file cause issues

sql serversql-server-2008-r2

Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files.

My understanding is the extensions don't really matter. Whatever is first is first and anything else is secondary regardless of the extension. Does that apply to .ldf in this case when clearly it's being used for the clustered indexes?

I ask because I would assume SQL Server treats .ldf differently than say, mdf's.

(And before you ask, yes, there is already a .ldf assigned for the transaction log on another spindle)

Best Answer

By convention, the following file extensions are used in SQL Server:

  • Primary Data File: .mdf
  • Secondary Data File(s): .ndf
  • Log File: .ldf

Having said that, you're actually free to use any file extension you like for any SQL Server file. In fact, it's completely fine to use no extension at all.

SQL Server does not look at or care about the file name in any way other than that it needs to be compliant with NTFS file name standards.

You can see what files are being used by a database with this query:

SELECT d.name
    , mf.name
    , mf.physical_name
    , SizeInMB = CONVERT(decimal(10, 2), mf.size) * 8192 / 1048576
    , FileGrowth = 
        CASE mf.growth 
        WHEN 0 THEN 'fixed size, will not grow'
        ELSE 
            CASE mf.is_percent_growth 
            WHEN 1 THEN CONVERT(varchar(10), mf.growth) + ' percent'
            WHEN 0 THEN CONVERT(varchar(30), mf.growth * 8192 / 1048576.0) + ' MB'
            END
        END
    , MaxSizeInMB = CASE mf.max_size
        WHEN 0 THEN 'fixed size, will not grow'
        WHEN -1 THEN 'file will grow until disk is full'
        WHEN 268435456 THEN 'log file will grow to maximum size of 2TB'
        ELSE CONVERT(varchar(30), CONVERT(decimal(10, 2), mf.max_size) * 8192 / 1048576.0) + ' MB'
        END
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = '<database_name>';

Replace <database_name> with the name of the database you're concerned about, or remove the entire WHERE clause to see files for all databases.

Related Question