LDF is IDF instead

Question

Post reply

LDF is IDF instead

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
February 26, 2013 at 8:08 am

#274568

The production database has 1 MDF and 1 log file.
The log file extension is idf instead of LDF. Would this cause issues?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15775 More actions · Answer 1

i havent heard of it, is the database accessible for you?

can you check the extension again?

Regards
Durai Nagarajan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You could call the transaction log file readme.txt if you wanted. SQL doesn't care what the name or extension of the file is in any way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 3

Interesting. I did not realize that. I am still going to relocate if off the data drive tonight and move it over to a dedicated log drive.

Thanks again Gail!

Leo.Miller SSChampion Points: 13046 More actions · Answer 4

Using MDF, NDF & LDF is really historical and convention more than anything else, it does however allow the files to be easily identified as database files. When you move the log file to the log drive you can rename the physical file, including the file extension(s) as shown below.

ALTER DATABASE MyDatabase

SET OFFLINE;

GO

-- for mdf

ALTER DATABASE MyDatabase

MODIFY FILE (NAME='MyDatabase', FILENAME='H:\Data\MSSQL.3\MSSQL\Data\MyDatabase.mdf');

GO

-- for ldf

ALTER DATABASE MyDatabase

MODIFY FILE (NAME='MyDatabase_Log', FILENAME='H:\Data\MSSQL.3\MSSQL\Data\MyDatabase_log.ldf');

GO

-- Move and rename the physical files before the next step as appropriate

ALTER DATABASE MyDatabase

SET ONLINE;

GO

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 5

I can't help myself - I have to ask:

Maybe it could be a lower-case "L" ("l") instead of an Upper-case "I" ?

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Perry Whittle SSC Guru Points: 233821 More actions · Answer 6

Vertigo44 (2/26/2013)
The production database has 1 MDF and 1 log file.
The log file extension is idf instead of LDF. Would this cause issues?

You just need to change the name in the SQL Server system catalogs and rename the OS file. Leo has detailed fully for you, you only need to complete the file rename and alter if you just want to rename the file. See my article at this link[/url] for more info.

GilaMonster (2/26/2013)
You could call the transaction log file readme.txt if you wanted

I bet someone, somewhere in the world has too 🙂

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Perry Whittle (2/27/2013)
GilaMonster (2/26/2013)
You could call the transaction log file readme.txt if you wanted
I bet someone, somewhere in the world has too 🙂

For the sake of playing around, I once created a DB where both the data and log files were alternate streams of a text file. Was silly, but it worked. CheckDB wouldn't run on it though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tony++ SSCarpal Tunnel Points: 4421 More actions · Answer 8

As noted previously, SQL itself doesn't care.

What does sometimes care is antivirus (not to start a thread on good idea or not, just that some shops do run AV and should be aware of this).

They can ignore certain file types by extension (either out or the box or by configuration), and it wouldn't know that IDF is a special file to ignore. That file gets scanned and activity to that file gets scanned. It's a non-obvious performance hit.

I can't comment on all AV packages, but I've had some in the past that would have created a problem with IDF.

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 9

Tony++ (3/27/2013)
As noted previously, SQL itself doesn't care.
What does sometimes care is antivirus (not to start a thread on good idea or not, just that some shops do run AV and should be aware of this).
They can ignore certain file types by extension (either out or the box or by configuration), and it wouldn't know that IDF is a special file to ignore. That file gets scanned and activity to that file gets scanned. It's a non-obvious performance hit.
I can't comment on all AV packages, but I've had some in the past that would have created a problem with IDF.

This is a good point. That file extension wouldnt be on the exclusions list.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 10

Just about all AV programs can be customised. If you did have a file with .idf extension just add it to the exclusion list

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Hemant.R Ten Centuries Points: 1286 More actions · Answer 11

As mention ,log file extension idf .Database is accessible but in case of attach detach process it might create problem .

Regards,

HEMANT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Hemant.R (3/29/2013)
As mention ,log file extension idf .Database is accessible but in case of attach detach process it might create problem .

It won't create any problems at any time. The ldf extension is a convention, nothing more. As far as SQL's concerned, the extension of the log file is irrelevant (it knows by checking the first section of the file whether a particular file is a log file or not)

This works fine, though is obviously silly. The only problems it'll cause is with the admins. Not recommended, but that's because people expect to see the common extensions used the normal way, not because of any problems it'll cause with SQL

CREATE DATABASE Weird ON PRIMARY

( NAME = N'Weird', FILENAME = N'D:\Develop\Databases\Weird.xlsx' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Weird_log', FILENAME = N'D:\Develop\Databases\Weird_log.txt' , SIZE = 1024KB , FILEGROWTH = 1024KB)

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233821 More actions · Answer 13

The only problem the attach will cause is the part where you open the file browser to locate the log file. The file browser will search for *.mdf and *.ldf so you'll need to change this to all files.

Apart from that, it makes no difference at all.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉