What are mdf,ndf,ldf files and how to see the data in those
files?
Answers were Sorted based on User's Feedback
Answer / visala
.mdf is extention of primary file,
.ndf is extention of secondary file,
.ldf is extention of log file,
sp_helpfile
Is This Answer Correct ? | 116 Yes | 10 No |
Answer / padma
When we create a db,the create command will create a data
file with an Extension .mdf,as well as a transaction log
file with extension .ldf
Any additional or secondary data files have .ndf extension
Is This Answer Correct ? | 121 Yes | 22 No |
Answer / ravi santlani
MDF is the main database files - It's a SQL Data files where all the data in the database objects are stored in. (like tables, stored procedures, views, triggers etc are stored in the mdf file of sql server). This is the reason whey we use .MDF file to attache the database. Once we attach the .mdf we were able to see all the data exist in that respective database.
LDF is the log files - The size of the log file(ldf) is determined my the logging level you have set up on the database. Simple, full, and buld logged are the options. Simple being the least, and full being the most. The log (if in full) will alow you to re-apply transactions to the databse incase of a failure. If your looking for some performance improvement, there are a lot of things that can be done.
Just to focus on the files you could more the location of the LDF file to a separate physical drive. This will increate performance because writing to the log and writing to the mdf files wont compete for each other for throughput tot he drive.
NDF are secondary databse files (Same as mdf, but you can only have 1 Main database file).
You can also create a NDF file for the databese, and change some of the tables to be stored on this NDF. This file also could be stored on a separate physical drive. Some advantages to this are:
Tables that are written to most frequently are stored in one file, and tables that are relatively static are in another. Having to smaller file to write to for transactions will help with throughput.
Is This Answer Correct ? | 46 Yes | 6 No |
mdf: When You Create Database .this Primary file
ndf: This is Sceondry File
ldf: Lof Files
Is This Answer Correct ? | 42 Yes | 9 No |
Answer / arunyadav007
For reading the log files (.ldf) an undocumented system
stored procedure sp_readerrorlog is there. This SP allows
you to read the contents of the SQL Server error log files
directly from a query window and also allows you to search
for certain keywords when reading the error file. This is
not new to SQL Server 2005, but this tip discusses how this
works for SQL Server 2005.
This is a sample of the stored procedure for SQL Server
2005. You will see that when this gets called it calls an
extended stored procedure xp_readerrorlog.
This procedure takes four parameters:
Value of error log file you want to read: 0 = current, 1 =
Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to
further refine the results
If you do not pass any parameters this will return the
contents of the current error log.
Example 1
EXEC sp_readerrorlog 6
This statement returns all of the rows from the 6th
archived error log.
Example 2
EXEC sp_readerrorlog 6, 1, '2005'
This returns just 8 rows wherever the value 2005 appears.
Example 3
EXEC sp_readerrorlog 6, 1, '2005', 'exec'
This returns only rows where the value '2005' and 'exec'
exist.
Even though sp_readerrolog accepts only 4 parameters, the
extended stored procedure accepts at least 7 parameters.
If this extended stored procedure is called directly the
parameters are as follows:
Value of error log file you want to read: 0 = current, 1 =
Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to
further refine the results
?
?
Sort order for results: N'asc' = ascending, N'desc' =
descending
Is This Answer Correct ? | 15 Yes | 16 No |
What are pessimistic lock and optimistic lock?
How do I open a .db file?
What are the different ways you can create databases in sql server?
Explain the concept of view and Types of views in SQL server?
What is history table in sql server?
Can you tell me the difference between DELETE & TRUNCATE commands?
If suppose in a table ship(name,year),find ship name having first and last name(i.e having space in between) ex: SAINT LOUIS,JET LAKE.
What is database replicaion? What are the different types of replication you can set up in SQL Server?
5 Answers Aptech, HCL, Perpetual, SAIC,
What are the basic features of a trigger in ms sql server?
List out some of the requirements to set up a sql server failover cluster?
can any one post me, how to remove rows in the below table ENO ENAME EDEPT ELOC 3368 BPS BI Adayar 3371 RAN BI valachari 3369 SRI BI valachari 3372 jay BI Chn - - - - - - - -
What is the difference between an index and a unique index?