SQL 2012 Autogrow and file extension problem

I had my data and log files set to auto grow by 10%.
This has caused the transaction logfile to grow to an astronomical 600 GB in size and led to the error

Autogrow of file PFMultilinguaklPOO_log in database 
PFMultilingual took 523826 milliseconds. 
Consider using ALTER Database to set a smaller FILEGROWTH for this file

This was causing DB timeouts.

I changed the auto growth setting from 10% to a fixed size to rectify this in the short term but I obviously need to shrink the size of my log file.
However when I went to investigate the best method of doing this I noticed a peculiar issue with the file extensions.

My data file has the .LDF extension and my log file has the .MDF extension which is obviously the wrong way round. I'm not sure how this happened. Can the extensions be changed back without any conflict?
I am attaching an image to show this.

I was wondering if anyone has seen this before and what the best way to resolve it is.

Can the extensions be changed back without any conflict?

Not while the database is active, but you can offline it, rename the files, then bring it back online:

• Check the virtual filenames as you'll need to refer to them in a later step:

SELECT name, physical_name, type_desc FROM my_db.sys.database_files

• Offline the database:

ALTER DATABASE my_db SET OFFLINE WITH ROLLBACK IMMEDIATE;

(note: the above will kick out other connections and active transactions, if you want to be a little safer to other potential users remove the "WITH ROLLBACK IMMEDIATE" and it will instead fail if there are other open connections)

• Rename the files in the filesystem

• Let SQL server know what you have done:

ALTER DATABASE my_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_db.mdf');
ALTER DATABASE my_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_db.ldf');

• Bring the database back online:

ALTER DATABASE my_db SET ONLINE;


I was wondering if anyone has seen this before

I've seen it. It is most likely that someone created the database by hand (rather than using SSMS or other tools), specified file locations (or just names) different to the defaults, but got the filenames slightly wrong. SQL Server would not have complained at all because is doesn't actually care what the filenames are, they could be named "B12746289863413584F2EFC7A9A35815" and "D040839B958143C890A760B5A489E944" and SQL Server would be just as happy to use them as instructed, the mdf/ndf/ldf and having meaningful names generally conventions are just to give us humans a visual clue as to the purpose of a given file.


what the best way to resolve it is.

As it doesn't upset SQL Server I would be tempted to say "just leave it alone" for production databases. For databases used for development/QA/UAT/other then either the above or alternately detach and re-attach the database instead of offlining it.

StackExchange Database Administrators Q#119296, answer score: 3