ldf created as TYPE ROWS DATA FILE in DATA filegroup where mdf file is

Question

.LDF was generated and has rows data type and in same file group where data mdf file is.
it is already containing information.

can this be fixed in a safe way?
can I safely remove the file ?
as I can check it says ldf type file but can I remove it?

Answer 1

.LDF was generated and has rows data type and in same file group where data mdf file is.

LDF is a common file extention for database log file; but it's only a file extention without any further meaning.
You can not create a log file in a datafile group, that's not possible. So it's a so called "secondary database data file" with a silly file ext.

For the rest: I don't understand what your issue is and what you are trying to achieve here; may can you explain it more detailed, please?

Answer 2

SQL Server does not enforce the extension name of files when they are created. You can name the file anything when it is created for the database. The "convention" is to name logs .LDF, but there is not a requirement or enforcement of that name.

To rename the file, see:
https://www.mssqltips.com/sqlservertip/4419/renaming-physical-database-file-names-for-a-sql-server-database/

Answer 3

No need to truncate or delete anything. At least as long you want to keep the secondary data file.

From a pure technical perspective, you can keep things as they are. Nothing will break.

However, it is a little disturbing from an aesthetic perspective. And there is also the risk that someone who comes after you may be confused, and possibly handles something incorrectly. So I would recommend that you change things.

These would be the commands:

ALTER DATABASE db MODIFY FILE (NAME = file1_log, NEWNAME = file2)
ALTER DATABASE db MODIFY FILE (NAME = file2, FILENAME = '<path>.file.ndfä)
ALTER DATABASE db SET OFFLINE

At this point you rename the file through Explorer.

ALTER DATABASE db SET ONLINE

I would recommend that before you take these steps in production, that you first exercise in a test environment, so that you are familiar with the steps. I should also add that I only typed these commands - I did not test them. Also read the link that Tom provided.

And to make it clear, the database must be taken offline for the operation, so you need to perform the operation in a maintenance window.

Answer 4

I am kind of worry to mix transaction log information with data information in this file.

This is nothing to lose sleep over. It cannot happen. A data file is a data file. And a log file is a log file.

On the other side if I just drop the file, will it be risk of loosing information?

You cannot drop the file as long as there is data in it. Well, you can stop SQL Server and delete the file. This means that your database will no longer be working, and you will need to restore a backup. So don't do that.

And I am not so aware of how will having another data file impact in a > positive way today in db performance.

Many people would say that it is good practice to have more than a single data file, although, I think the recommendation is rather to have more filegroups than to add a file to the PRIMARY filegroup. When it comes to performance, it can also help to have multiple files. I am not aware of that it would have a negative impact.

However, from a manageability perspective, life is easier if all databases have exactly two files, one data file and one log file. If you want to get rid of this file entirely, it seems that you can do this:

DBCC SHRINKFILE(file1_log, EMPTYFILE)
ALTER DATABASE db REMOVE  FILE file1_log

Again, I recommend that you first test in a test environment.

Answer 5

Hi @MEMO ,

Full agree with others.

> I am kind of worry to mix transaction log information with data information in this file.

Don’t worry about that. SQL server log information is written to the log file in sequence. The log information will only be written to the first log file, it will not be written to other log files. And as Erland mentioned, data file is data file , log file is log file. SQL server will not mix them.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".