3

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. enter image description here

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

  • If you go to the physical path, what filename is shown there? what extension does this file have? From the sizes of your log compared to your db it seems that you don't take regular log backups, do you? – RayofCommand Oct 27 '15 at 11:30
  • The physical path it has the reverse extension as well.After more checking I found that the backups I had set in my Maintenance plan were failing and hence the logfile size. – Paul Oct 27 '15 at 16:07
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.

  • Thanks for your advice it makes sense. I was thinking of doing a restore but as this is a production database I don't have the time to schedule that in at present. But I will do it eventually to keep it consistent. – Paul Oct 27 '15 at 16:14
  • you can always script a deattach, rename, reattach in powershell so it is done in under 1 minute just FYI. Let us know if the answer was correct by marking it or if not, let me know how else we can help! Good luck. – Ali Razeghi Oct 27 '15 at 16:16
  • You are right on the transaction log file size, my backups were failing. I will fix that and change the db recovery mode to simple. I don't think I need the point in time recovery. – Paul Oct 27 '15 at 16:16
  • Thanks. I have marked the answers as correct but it is saying I need 15 reputation points for them to display. Sorry I'm new to this forum. – Paul Oct 27 '15 at 16:20
  • absolutely no problem. Please let me know if you have any further issues. – Ali Razeghi Oct 27 '15 at 18:34
3

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.

  • Thanks for the solution. It is a production database so I will take your advice and leave the extensions as they are and tackle the log file size. – Paul Oct 27 '15 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.