Andrew Jackson General topics

Methods to Repair Corrupted MDF and LDF Files


MS SQL Server is a relational database management system that is used for storing and retrieval of data when requested by other applications. The SQL database mainly comprises of three files- MDF, NDF and LDF. The first two files from the primary and secondary database of SQL Server and the latter stores the log database of SQL server database transactions. While the data files and the log file are accessed by a common server, these files do not share the same location for their storage. Both the data files and the log file are associated with SQL server; in this blog however, we will restrict our discussion to how to repair corrupted .mdf and .ldf files.

Primary & Log Database of SQL Server

Primary database aka Main Database File (MDF) as the name suggests is the main database file of SQL Server that are associated with other files. There exists only a single MDF file for every SQL Server database, which stores data items like triggers, views, stored procedures, etc. It would not be wrong to say that MDF file is the primary element for administration of SQL database.

The LDF (Log Database File) store the entire log information of the transactions or activities carried out in the SQL Server. They play a very crucial role in the restoration of SQL server data in cases of data disaster. The size limitation of LDF is decided at the time of the database designing. Information related to records updation, record deletion, insertion of new record, etc.

Corruption in MDF and LDF Files

Maintaining the database integrity of SQL server is the top priority of the users as any mishap can lead to loss of data that at times cannot be recovered easily. There can be umpteen reasons, which can end the MDF and LDF files in a corrupted state. Some of the reasons for MDF and LDF corruption are:

  1. The biggest reason behind the corruption of MDF and LDF files is the storage size. In case the data exceeds the limit of MDF and LDF file, corruption is likely to occur.
  2. In case the SQL server database is modified during the DROP operation, there are great chances of MDF and LDF files corruption.
  3. Hardware issues such as problem in the hard drive sector storing the SQL database, may lead to corruption of the database.
  4. Virus intrusion is also an important factor when it comes to corruption of MDF and LDF files.

Manual Solutions to Repair Corrupted MDF and LDF Files

In order to repair a corrupt MDF and LDF file, DBCC CHECKDB command can be used. It recovers the data from a corrupt SQL database file that has been damaged due reason such as logical corruption. This involves running an executable code in the SQL server in order to repair corrupted MDF and LDF files.

dbcc-checkdb

Note: Before running the DBCC CHCKDB command, the backup of the existing database should be taken.

In case the repair process fails and the backup of the MDF and LDF files does not exist, the user will not be able to restore the database back to its original state and hence will end up in having a corrupt MDF and LDF file.

Alternate Solution to Repair Corrupted MDF and LDF Files

Regarding the drawbacks of the manual procedure to repair corrupted MDF and LDF files, third party solutions are preferred over them. One such commercial utility that can be deployed for repair corrupted MDF and LDF files is SQL Server Database Recovery Tool. The software recovers the entire data from corrupt MDF and LDF files and save it in SQL compatible scripts or directly in SQL server database.

Andrew Jackson General topics, Log Shipping

Know How to Monitor Log Shipping in SQL Server


Log shipping in SQL Server is the process of enabling an automatic backup of the complete database and transaction files of the primary server and then restoring the backup on a secondary server. Being a high-availability solution, it increases the database availability of SQL Server by creating a backup server that can replace the primary server in case of data disasters.

Log Shipping involves 3 servers:

  1. Primary or the source server
  2. Secondary or destination server
  3. Monitor Server

Log Shipping in SQL Server allows sending of transactional log backups from primary instances residing on the primary server to one or more secondary instances residing on the secondary server. These transactional log backups are applied to each instance of the secondary database individually.

It is to be noted that Log shipping in SQL Server can also be configured without the installation of monitor server. In such a case, the history and the status of all the backup operations are stored on the primary server and the history and restore status is stored on the secondary server. However, when the remote monitor server is installed, this information also gets stored on it. It is also responsible for raising alerts whenever the backup process is not performed as per the schedule.

In case the monitor server has not been specified, the primary and secondary servers monitor the backup and restore operations, and raise the alert when an error is encountered.

Key Note for Monitoring Log Shipping in SQL Server

For monitoring the configuration of log shipping in SQL Server, the monitor server needs to be added at the time of enabling log shipping. In case the monitor server is added later on, the existing log shipping configuration must be removed and the new setup including monitor serves must be installed. In addition to this, any alterations in the monitor server can only be enabled after removing log shipping.

History Tables in SQL Server Log Shipping

The monitoring history tables of log shipping comprise metadata that gets stored on the monitor server. Moreover, primary and secondary server-specific information is also stored locally. For monitoring the status of the monitoring sessions, these history tables are brought in use. In order to check the status of log shipping, status and history of backup job, copy and restore functions need to be viewed.

Some of the tables that need to be checked for monitoring the log shipping in SQL Server are:

  1. log_shipping_monitor_alert

    2. This table stores the alert job ID.

  2. log_shipping_monitor_error_detail

    3. This table stores the details of the log shipping transactions. In addition to this, the users can also access the table for accessing errors in a session.

  3. log_shipping_monitor_history_detail

    4. This table gives the details of the history of the log shipping agents. Details regarding an agent session can also be viewed in this table.

  4. log_shipping_monitor_primary

  5. One monitor record for primary database of each log shipping configuration is stored in this table. This includes the information like the details of the last backup and the last restored file.

  6. log_shipping_monitor_secondary

    7. This table stores one monitor record of the secondary database like the last backup file and the last restored file that are used in monitoring.

Conclusion

Monitoring log shipping in SQL Server enables the administrator to monitor the entire configuration of the entire environment. In addition to the history tables, additional parameters like stored procedures enable to monitor log shipping in SQL Server.

Mustafa EL-Masry General topics

SQL Trivia – Difference between COUNT(*) and COUNT(1)


Manoj Pandey's avatarSQL with Manoj

Yesterday I was having a discussion with one of the Analyst regarding an item we were going to ship in the release. And we tried to check and validate the data if it was getting populated correctly or not. To just get the count-diff of records in pre & post release I used this Query:

To my surprise he mentioned to use COUNT(1) instead of COUNT(*), and the reason he cited was that it runs faster as it uses one column and later uses all columns. It was like a weird feeling, what to say… and I just mentioned “It’s not, and both are same”. He was adamant and disagreed with me. So I just kept quite and keep on using COUNT(*) 🙂

But are they really same or different? Functionally? Performance wise? or by any other means?

Let’s check both of them.

The MSDN BoL lists the syntax as

View original post 314 more words

Mohit SQL Server 2012, SQL Server 2014, SQL Server 2016

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group


One of customers changed the value returned from @@SERVERNAME.  SQL Server works no problem, however an unexpected behavior appeared.  Changing the value for @@SERVERNAME, caused the backups to fail.

Looking at the maintenance jobs, found all jobs completed successfully and without issues.  However, upon looking at the database’s statics it states no backups completed.

image

Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node.  However, it returns value of 0 for all databases, if the preferred replica is set.  Because, the script makes a check that is running on the server that is preferred.  It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas.  Because value will never match, it skips the database on both primary and secondary replica.

I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link).  There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link).   Please vote!

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Mustafa EL-Masry General topics

Microsoft Developers Conference in Egypt 2016


945578_10153817280998480_1718251064609551794_n

Microsoft CEO Satya Nadella is visiting Egypt as a special keynote speaker during the Microsoft Developers Conference, which DONE on the 5th of January, 2016. The conference is the perfect opportunity to brush up on the latest tools, technologies and topics such as big data, machine learning, IoT and cross platform development.

Microsoft Developers Conference is meant to bring the Top IT industry Influencers and brush up on the latest tools and technology, such as Big Data, Machine Learning, Internet of Things, and Cross Platform Development.

 

 

12393746_1226203387394797_463369179_n

Microsoft’s C.E.O., Satya Nadella,Greetings from Cairo. Today I met with Walid Khairy and Ahmed Najeeb, developers and co-founders of indie game studio 2024. Egypt is becoming an innovation hub within the region, and it was great to see the creativity and passion of Egyptian developers and startups. -SN – See more at: http://iconosquare.com/p/1155976999660073731_524549267#sthash.L8aepkub.dpuf