February 26, 2013 at 8:08 am
The production database has 1 MDF and 1 log file.
The log file extension is idf instead of LDF. Would this cause issues?
February 26, 2013 at 8:58 am
i havent heard of it, is the database accessible for you?
can you check the extension again?
Regards
Durai Nagarajan
February 26, 2013 at 9:15 am
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
February 26, 2013 at 9:49 am
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!
February 26, 2013 at 3:00 pm
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.
February 27, 2013 at 5:15 am
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
February 27, 2013 at 5:27 am
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" 😉
February 27, 2013 at 6:47 am
Perry Whittle (2/27/2013)
GilaMonster (2/26/2013)
You could call the transaction log file readme.txt if you wantedI 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
March 27, 2013 at 3:10 pm
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.
March 27, 2013 at 3:29 pm
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.
March 27, 2013 at 3:40 pm
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" 😉
March 29, 2013 at 3:54 am
As mention ,log file extension idf .Database is accessible but in case of attach detach process it might create problem .
Regards,
HEMANT
March 29, 2013 at 5:13 am
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
March 29, 2013 at 8:56 am
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" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply