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.

  3. log_shipping_monitor_error_detail
  4. 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.

  5. log_shipping_monitor_history_detail
  6. 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.

  7. log_shipping_monitor_primary
  8. 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.

  9. log_shipping_monitor_secondary
  10. 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.


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.

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

SQL 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

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.


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.

Microsoft Developers Conference in Egypt 2016


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.




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:










WordPress Annual Report to my SQL Server Community

I completed 4 Years as SQL Server blogger with my small Community SQL Server Consultation Community (

I started it from 2012 and I will not stop sharing the information ad the new Technology in SQL Server

You can check the WordPress annual Report for the last 4 years from here


WordPress Annual Report to my SQL Server Community