In SQL Server Records (Data) and log information is not stored in one file. There are three types of individual files used by SQL server to store its information. These files are Primary data file and secondary data file. These two individual files are used by one SQL server. These different files have their different file extensions. Below is a brief description of these two files.
Primary data files (.Mdf file)
In a nutshell, .Mdf, also known as a master data file of SQL server and is the Primary database file where the data stored physically. It is a very important file of SQL server. Because it stores very crucial information of the database like tables, views, stored procedure, triggers… etc. and it also links to other database files. In another way we can say, a Primary data file is a file which contains the schema with data. It is necessary to have one primary data file in every database.
When we create a database through create command, it creates a data file with .Mdf extension. This extension is recommended for Primary data file.
Secondary data files (.Ndf file)
The Ndf file of the database is also known as a secondary data file. These are optional and user defined data files and also store users' data. It is not important to have a secondary data file. If a database uses maximum size for a single file, then we can use secondary data file. Therefore, some databases not required any secondary data file. A simpler way, we can say that a secondary data file is an extra file by which you can continue to grow your database.
While it is not necessary to have a secondary data file, but you can have more than one secondary data file for a single database.The recommended file extension for a secondary data file is .Ndf.
Note:Though the required file extensions for these two files is .Mdf and .Ndf. The SQL server Never enforces you to use these file extensions.
These two files (Primary and secondary) are located and stored in the Primary file of the database and in the master database. Whenever a SQL server database engine wants to use file location of those two files, it retrieves the data from the master database.
Sometimes complete database inaccessibility may cause of corruption in any of these data files.
.Mdf and .Ndf File Corruption issues on SQL server
The SQL Server database files (MDF and NDF) involve important data so they should be well maintained. It should be frequently retrieved, managed and stored, so the integrity maintenance of the data is a very important term in SQL server database. If it is not managed properly, it can be cause of corruption
There are many corruption issues arising in SQL Server. Some of the common error messages that could appear while accessing corrupt MDF file are mentioned below.
Error messages:
- Table error: Object ID 0, index ID 0, and page ID (1:105). The PageId in the page header = (0:0)
- Buffer provided to read column value is too small
- Conflict, occurred in database'db_name', table 'table_name', column 'column_name'
- Memory or buffer error, space provided to read column is too small
In such circumstances, you should use the DBCC CHECKDB command-line tool to repair data corruption.
Run following command to check the physical consistency of the database:
'DBCC CHECKDB ('neo') WITH PHYSICAL_ONLY; GO'
The two manual repair options used with DBCC CHECKDB are:
- REPAIR_ALLOW_DATA_LOSS
- This option will usually try to repair all reported errors and might also cause some data loss. This option only uses as the last resort
- REPAIR_REBUILD
- This option does not elevate the data loss. For example, if a non-clustered index is damaged, you can be easily repaired the index by rebuilding it.
If the methods discussed above fail to resolve the problem, use third party tool SysTools SQL Recovery software.
