SQL SERVER DATABASE FILE AND FILEGROUPS

SQL Server handles the bulky database of organizations. SQL supports two operating files one is data file and another is log files. There are three different formats for each file mdf and ndf file formats are for data files and all logs files are stored as .ldf format. The data file contains all the records, tables, indexes, views and stored procedure and all the transactions taken place in the database are being stored in log files. Later these log files can be used to recover all the transaction done in the database. As SQL Server is the best application but the default fact is that this is not immune to corruption. Mostly the issue involve in corruption is the repair of database so for this user mostly go for manual repair but there are certain issues involved with manual solution which may leads to failure of the manual solution in recovering database. So SQL Database Repair can be used in this case.

Types of database Files in SQL Server

SQL support three file formats for data and log storage. For the primary data .mdf files are used it store the master data and also known as master data files and for the secondary database .ndf files are defined. All the transaction information or can say log data are stored using .ldf format and referred as log files or transaction log files. Basically your secondary database files are optional one and the master database files are the permanent one. That means a database will surely have one .mdf file but the existence of .ndf is optional.

Types of Filegroups in SQL Server

SQL Server supports two filegroups. There is a primary filegroup which exist for every database and secondary filegroups are being created by the user. The primary filegroup contains all the primary files and system files are also allocated in the Primary filegroup. The user-defined filegroup contain secondary files created by the user.

SQL SERVER ERROR 5228

Error: 5228, Severity: 16, Table error: object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page %S_PGID, row %d. DBCC detected incomplete cleanup from an online index build operation. (The anti-matter column value is %d.)

Most of the database user’s are customary to this error message while using the database. The Error 5228 occurs when intermediary state leads to the corruption of database. In SQL Server occurrence of errors while managing bulky data is a customary event, but the data recovery is crucial issue.

Reasons of Occurrence

SQL Server error 5228 occurs when intermediary state leads to the corruption. That means the database become in-accessible and display error message 5228 as shown above. As seen mostly the error 5228 occurrence is seen in SQL Server version 2000 and all above. Whenever the user perform delete, modify and add command on database and a partition index that is unfinished is identified in the PN_ID, O_ID object and I_ID index partition then intermediary state occurs leading to error 5228.

SOLUTIONS 

Mostly the user when identify this error state go for manual solutions such as Hardware diagnose, Backup restoration and run DBCC CHECKDB command. Most of the time these solutions are able to recover data but in some cases where these manual solutions are not a success SQL Repair can be helpful and to know more read  Error 5228 Blog.

SQL MASTER DATA SERVICE

Microsoft SQL Server is the most preferred database management system use by the database user’s and large enterprise organization to manage and store their useful data effectively.Microsoft SQL Server support three file formats for different data. Primary data are stored in the form of .mdf file, secondary data is being stored in the .ndf file and all the transaction log files are stored in .ldf files. The master database management is done by the Master Data Service provided by the Microsoft SQL Server. As seen that the management of the large bulky database is not that easy it, as it not a doubt on the efficiency of Microsoft SQL Server but the unfortunate truth is that this most efficient database management and storage application is not immune to the corruption. So sometimes these master database files get corrupted so in that case user goes for a SQL Repair.

WHAT IS MASTER DATA SERVICE?

All the efforts done by an organization to define and discover the list of data (non-transactional lists) are described by the master data management to compile the maintained master lists. So the Microsoft SQL Server solutions for MDM (Master database Management) is your Master Data Services. Master Data Services can be used for managing Domain (As the service is not specified to managing lists of products, customer or accounts).

Components and Tools of Master Data Services

The Master Data Services offered by Microsoft SQL include many tools and components which are listed below.

1.  Master Data Service Add-in (For Excel):- Used to create new attributes and entities as well as manage the data.

2. Master Data Service Configuration Manager: - A tool used to configure and create MDS web applications and database.

3. Master Data Manager:- Basically a web application used to perform tasks (administrative) used by the user to update data

4. Master Data Services Web Service: - Used by the developer to build or extend custom solutions that are used by Master Data Service.

5. MDSModelDeploy.exe:- A tool used to make packages of the model objects and data which can be used in other environment.

SQL Server Transaction Log

Thinking of a database management system, a mission critical application and a breakthrough perform, the first application which strikes the mind of the user is SQL Server. SQL Server is best rated database management application which provide storage and management of database. SQL support three different file formats, MDF for primary database NDF for secondary database and LDF for the transaction logs or can say for log database. At the time when your database gets corrupted you can recover your full data by using the transaction log saved as .ldf format.

Note: - But sometimes this backup is not maintained so then at the time of corruption other methods are used to repair corrupt data.

Transactions Log: - Basically transaction log contain all the records of the transaction operations done on your database. Truncation should be done on transaction log to reduce the size of these files.

Supported Operations by Transaction:-  All the operations supported by transaction log are listed below.

•   Individual transaction recovery.
• Transaction replication support.
•  It recovers all pending transactions when SQL Server is started.
• Database mirroring, log shipping and Always on groups are some recovery and               high availability solutions.

Truncation in Transaction log: - Truncation done in transaction log will free the space in the log files. In this process the in-active virtual log files are deleted so to avoid transaction log filling. So truncation of transaction log is an important process to avoid the filling of full disk space allocated to transaction log. The delay in this process can cause problem so there is automatic start of this process by certain events that are listed below.

•  When check-point occurs in the simple recovery model than truncation occurs.
• In the case of full and bulked-logged recovery model when log back-up occurs the truncation of transaction log take place.

As mentioned above truncation take place when virtual log are inactive but sometimes the long activeness of the logs leads to deal in this truncation process. But sometimes we need to analysis the transaction files so for that can use Log Analyzer Tool.