SQL 2012 Autogrow and file extension problem

Question

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.

Answer 1

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:

1. 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
2. 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)
3. Rename the files in the filesystem
4. 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');
5. 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.

Answer 2

SQL Server doesn't care what extension you give the files but for consistency sake and for the sanity of anyone who manages this, it's best to fix it. If you can have scheduled downtime it's the easiest. For a DB of this size I would just backup, test restore, restore with overwrite and during the restore specify the new filename. You can also deattach and reattach but that method will have support removed at some point.

It seems like your transaction log is growing way too much. I think your DB is in the BULK or FULL recovery model which means you have to take transaction log backups. If you do not need point in time recovery, please set your DB to SIMPLE recovery model and run a checkpoint, then back up.

Also your log file is probably very heavily fragmented with VLFs right now. You will want to fix that as it will make restore times longer and make the tlog throughput perform at a lower level.

VLF Throughput Optimization:

Reducing VLFs - basic

Setting Simple Recovery Model Note: Follow up with the stakeholder to ensure you don't need point in time recovery, if you do, look up transaction log backups.